topic Re: Look up data with multiple values in QlikView

Look up data with multiple values

veldetta — Wed, 06 Sep 2017 17:32:23 GMT

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:

AAAA;BBBB;CCCC

DDDD

EEEE

FFFF;HHHH

IIII

Table 2:

AAAA

BBBB

CCCC

DDDD

FFFF

GGGG

HHHH

Desired Result:

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

HHHH

Yes

FFFF;HHHH

How would i go about getting the desired result?

Re: Look up data with multiple values

Gysbert_Wassenaar — Wed, 06 Sep 2017 17:57:20 GMT

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;

Re: Look up data with multiple values

veldetta — Wed, 06 Sep 2017 23:27:40 GMT

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

Re: Look up data with multiple values

techvarun — Thu, 07 Sep 2017 10:18:26 GMT

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

Re: Look up data with multiple values

techvarun — Thu, 07 Sep 2017 10:20:05 GMT

Output

Re: Look up data with multiple values

qliksus — Thu, 07 Sep 2017 10:58:57 GMT

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

Dimension: ID , Name

Expression: Concat( Distinct Match ,',')

Re: Look up data with multiple values

effinty2112 — Thu, 07 Sep 2017 11:11:46 GMT

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

Re: Look up data with multiple values

Gysbert_Wassenaar — Thu, 07 Sep 2017 12:45:37 GMT

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

AAAA

CCCC

Table 2

AAAA;BBBB;CCCC

Re: Look up data with multiple values

veldetta — Thu, 07 Sep 2017 12:58:20 GMT

Yes should show if part of it is matched.

Re: Look up data with multiple values

Kushal_Chawda — Thu, 07 Sep 2017 15:00:06 GMT

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 [

AAAA

BBBB

CCCC

DDDD

FFFF

GGGG

HHHH ];

Final:

LOAD Distinct

ID,

Name,

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

Resident Data;

DROP Table Data;

Re: Look up data with multiple values

veldetta — Mon, 11 Sep 2017 16:47:03 GMT

Thank you all for your responses.