Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have two tables in my scenario.
Table 1 is having all the Data
and Table 2 has only one field (or a List of Values)
Table1
Column 1 | Column 2 | Column 3 | Column 4 |
---|---|---|---|
1 | 4 | 7 | ABC - A |
2 | 3 | 4 | ABC - B |
6 | 6 | 2 | ABC - C |
3 | 6 | 7 | PQR - JKL - A |
23 | 3 | 5 | PQR - JKL - B |
54 | 3 | 2 | ABC |
23 | 7 | 9 | PQR - JKL |
6 | 3 | 8 | XYZ - A |
7 | 1 | 5 | DEF - B |
Table2
ColumnA |
---|
ABC |
PQR - JKL |
DEF |
If any value in Table 2 ColumnA matches with any value of Table 1 Column4 ( excluding '- A', '- B' , '- C' ) then it should have value 1 else 0.
Expected Result :
Column 1 | Column 2 | Column 3 | Column 4 | Result |
---|---|---|---|---|
1 | 4 | 7 | ABC - A | 1 |
2 | 3 | 4 | ABC - B | 1 |
6 | 6 | 2 | ABC - C | 1 |
3 | 6 | 7 | PQR - JKL - A | 1 |
23 | 3 | 5 | PQR - JKL - B | 1 |
54 | 3 | 2 | ABC | 1 |
23 | 7 | 9 | PQR - JKL | 1 |
6 | 3 | 8 | XYZ - A | 0 |
7 | 1 | 5 | DEF - B | 1 |
Since these are two different tables direct if clause is not possible.
Pls help.
Thanks and Regards,
Anjali Gupta
TableB:
Load Concat(ColumnA, '|') as Key_Match;
LOAD trim(subfield(ColumnA, '-')) as ColumnA
Inline [
ColumnA
ABC
PQR - JKL
DEF
];
LET v = Peek('Key_Match');
TableA:
LOAD *, -1*(Index('$(v)', NewColumn4)>0) as MatchFlag
WHERE NOT match(NewColumn4, 'A', 'B', 'C');
LOAD *, trim(SubField(Column4, '-')) as NewColumn4
INLINE [
Column1, Column2, Column3, Column4
1, 4, 7, ABC - A
2, 3, 4, ABC - B
6, 6, 2, ABC - C
3, 6, 7, PQR - JKL - A
23, 3, 5, PQR - JKL - B
54, 3, 2, ABC
23, 7, 9, PQR - JKL
6, 3, 8, XYZ - A
7, 1, 5, DEF - B
];
if you want your original table you can add at the end of the script this
TableAA:
noconcatenate load Column1, Column2, Column3, Column4, max(MatchFlag) as MatchFlag
Resident TableA
group by Column1, Column2, Column3, Column4;
drop table A;
Without sample data or app it is very difficult to suggest a solution. But give a try with the following code and see if it helps. I made few changes.... Hope this helps...
TableA:
LOAD *,
subfield(Column4, ' ' ,1) as Key_Match
INLINE [
Column1, Column2, Column3, Column4
1, 4, 7, ABC - A
2, 3, 4, ABC - B
6, 6, 2, ABC - C
3, 6, 7, PQR - JKL - A
23, 3, 5, PQR - JKL - B
54, 3, 2, ABC
23, 7, 9, PQR - JKL
6, 3, 8, XYZ - A
7, 1, 5, DEF - B
];
TableB:
Load *,
subfield(ColumnA, ' ' ,1) as Key_Match
Inline [
ColumnA
ABC
PQR - JKL
DEF
];
Temp:
NoConcatenate
Load * Resident TableA;
Left Join (Temp)
Load * Resident TableB;
Drop Tables TableA,TableB;
Final:
NoConcatenate
Load Column1,
Column2,
Column3,
Column4,
Key_Match,
If(WildMatch(ColumnA,Left(Column4,3)&'*'),1,0) as Result
Resident Temp;
Drop Table Temp;
Hi,
one solution might be:
Table2:
LOAD * FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @2);
mapColA:
Mapping
LOAD ColumnA, '@ColumnAExists@'
Resident Table2;
Table1:
LOAD *,
-(MapSubString('mapColA',[Column 4]) like '*@ColumnAExists@*') as Result
FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @1);
hope this helps
regards
Marco
Hi Anjali,
One more solution from my side.
Table2:
LOAD Concat(Chr(39) & '*' & ColumnA & '*' ,',') as ColumnA
FROM [https://community.qlik.com/thread/240758]
(html, codepage is 1252, embedded labels, table is @2) where Len(Trim(ColumnA))>0;
Let vMatch = Peek('ColumnA',0,'Table2');
DROP Table Table2;
Table1:
LOAD *,
If(WildMatch([Column 4],$(vMatch)),1,0) as Result
FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @1);
Set vMatch =;
Result:
Hi Thirumala,
This won;t work when I have value like
PQR - JKL - A
PQR - GHI - A
Where I have to match PQR - JKL and PQR - GHI seperately.
Thanks and Regards,
Anjali Gupta
Hi Marco,
This works for me. Thanks a lot.
Could you pls brief in on '@ColumnAExists@' , What are these values/ variables called?
And how these are used.
Thanks and Regards,
Anjali Gupta
Hi,
the string '@ColumnAExists@' in this case is just a marker to identify matching substrings.
It only has to be improbable to occur in your data but it could be any string. So there's no special meaning to this particular string I used. To get a random marker string you could also use something like this:
mapColA:
Mapping LOAD ColumnA, Hash128(1)
FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @2);
Table1:
LOAD *, Sign(Index(MapSubString('mapColA',[Column 4]),Hash128(1))) as Result
FROM [https://community.qlik.com/thread/240758] (html, codepage is 1252, embedded labels, table is @1);
hope this helps
regards
Marco
Thanks Marco.
Regards,
Anjali Gupta