Joining on Individual characters in a column

Wojciech Glowacki
Wojciech Glowacki Contributor
edited May 29 in General

Hi again,

I have 2 datasets. One has columns Land1 and ZWELS. ZWELS can have multiple characters entered as you see on the first screenshot. The second Data set has also LAND want and the column ZLSCH which can only have a single character. What I want to to is join my datasets on LAND1 + every single character from ZWELS with ZLSCH. So if in ZWELS I have in one record EQY I ahve to mach the E with the E from ZLSCH, the Q with the Q from ZLSCH and the Y with the Y from ZLSCH. Each of those characters has a corresponding name value in the dataset which for priviacy reasons I wont paste in. But for the Combination of LAND1 + the value from ZWELS (in this example EQY) to want to see all 3 name values joined for that record.

Somthing like DE

EQY

Text1 related to E from the second dataset

Text2 related to Q from the second dataset

Text1 related to Y from the second dataset

image.png image.png

Answers

  • Wojciech Glowacki
    Wojciech Glowacki Contributor

    Sorry for some reason I cannot edit the text anymore. This is the result I am hoping for

    image.png
  • James T Sidebotham
    James T Sidebotham Contributor

    Hi,

    I made some dummy data and was able to do what you wanted (there was no Q in your list for second dataset so I made LAND1=QQ, ZLSCH=Q). Not sure what you meant between Text1 and Text2 (I just assumed it was a typo)

    image.png

    My approach:

    I assumed that ZWELS would not be more than 3 chars long, and so created 3 new columns (POS1, POS2, POS3) based on the positions:

    image.png

    Then I did a lookup on dataset2, which resulted in what you saw above.

    image.png

Categories