Look up data with multiple values

[veldetta]

I have 2 tables.  1 table has multiple values in 1 column seperated by a ';' (semi-colon).  I need to match/look up another table that has one of the values.

Table 1:

ID

AAAA;BBBB;CCCC

DDDD

EEEE

FFFF;HHHH

IIII

Table 2:

ID

AAAA

BBBB

CCCC

DDDD

FFFF

GGGG

HHHH

Desired Result:

ID	Match	Matching Field
AAAA	Yes	AAAA;BBBB;CCCC
BBBB	Yes	AAAA;BBBB;CCCC
CCCC	Yes	AAAA;BBBB;CCCC
DDDD	Yes	DDDD
FFFF	Yes	FFFF;HHHH
GGGG	No
HHHH	Yes	FFFF;HHHH

How would i go about getting the desired result?

[Gysbert_Wassenaar]

Table1:

LOAD

     ID as MatchingField,

     SubField(ID,';') as ID

FROM

     SourceTable1

     ;

RIGHT JOIN (Table1)

LOAD ID FROM SourceTable2;

Result:

LOAD

     ID,

     If(Len(MatchingField)>0, 'Yes', 'No') as Match,

     MatchingField

RESIDENT

     Table1

     ;

DROP TABLE Table1;

[veldetta]

Thank you!  I believe you're on the right track, however, I didn't explain it correctly.  So I'll try again... I have 2 tables:

Table 1:    

ID	Name
1	AAAA;BBBB;CCCC
2	DDDD
3	EEEE
4	FFFF;HHHH
5	IIII

Table 2:  

Name

AAAA

BBBB

CCCC

DDDD

FFFF

GGGG

HHHH

Desired Result:

ID	Name	Match
1	AAAA;BBBB;CCCC	Yes
2	DDDD	Yes
3	EEEE	No
4	FFFF;HHHH	Yes
5	IIII	No

[techvarun]

Sheet1:
LOAD ID,SubField([Name],';') as Name, Name as NameFull, if(isnull(Previous([Name]))<>0,chr(39)&[Name]&chr(39),Peek(previous_Name)&','&chr(39)&[Name]&chr(39)) as previous_Name INLINE [
 ID, Name
    1, AAAA;BBBB;CCCC
    2, DDDD
    3, EEEE
    4, FFFF;HHHH
    5, IIII


];


Sheet2:
LOAD [Name1], if(isnull(Previous([Name1]))<>0,chr(39)&[Name1]&chr(39),Peek(previous_Name1)&','&chr(39)&[Name1]&chr(39)) as previous_Name1
 INLINE [
    Name1
    AAAA
    BBBB
    CCCC
    DDDD
    FFFF
    GGGG
    HHHH
];
MaxStringSheet_Name:
load MaxString(previous_Name) as max_Name
Resident Sheet1;


let vmax_Name=Peek('max_Name',-1);


NoConcatenate
FinalSheet1:
load ID,NameFull,[Name] as final_Name, if(Match([Name],$(vmax_Name1))>0,'Yes','No') as [New_Column_Name]
Resident Sheet1;


DROP TABLE Sheet1,Sheet2, MaxStringSheet_Name;

Reference : Compare two column values and create a new column

[techvarun]

Output

[qliksus]

Folow the approach of Gysbert and use the folllwing in the chart

Dimension: ID , Name

Expression: Concat( Distinct  Match ,',')

[effinty2112]

Hi Trevel,

Here's an alternative:

[Table 1]:

LOAD * Inline [

ID, Name 

    1, AAAA;BBBB;CCCC 

    2, DDDD 

    3, EEEE 

    4, FFFF;HHHH 

    5, IIII

    ];

TempElements:

LOAD

ID,

SubField(Name,';') as NameElement

Resident [Table 1];

Inner Join(TempElements)

LOAD * Inline [

  NameElement 

    AAAA 

    BBBB 

    CCCC 

    DDDD 

    FFFF 

    GGGG 

    HHHH

    ];

    MappingMatch:

    Mapping LOAD

    Distinct

    ID,

    'Yes'

    Resident TempElements;

    Drop Table TempElements;

    Left Join([Table 1])

    LOAD

    ID,

    ApplyMap('MappingMatch',ID,'No') as Match

    Resident [Table 1];

Giving:

ID	Name	Match
1	AAAA;BBBB;CCCC	Yes
2	DDDD	Yes
3	EEEE	No
4	FFFF;HHHH	Yes
5	IIII	No

Cheers

Andrew

[Gysbert_Wassenaar]

Do you want Match to be Yes if at least one part is matched or only when all parts are matched?

What happens when you have this data:

Table1

ID

AAAA

CCCC

Table 2

ID

AAAA;BBBB;CCCC

[veldetta]

Yes should show if part of it is matched.

[Kushal_Chawda · Accepted solution]

try this

Data:

LOAD *,SubField(Name,';') as MatchID;

LOAD * Inline [

ID, Name

1, AAAA;BBBB;CCCC

2, DDDD

3, EEEE

4, FFFF;HHHH

5, IIII ];

Left Join(Data)

LOAD ID as MatchID,

     'Yes' as Match;

LOAD * Inline [

ID

AAAA

BBBB

CCCC

DDDD

FFFF

GGGG

HHHH ];

Final:

LOAD Distinct

ID,

Name,

if(isnull(Match),'No',Match) as Match

Resident Data;

DROP Table Data;