Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community!
I have two tables that I would like to join in a third table:
Table A
Header 1 | Header 2 |
---|---|
BS0000 | 22 |
BS0000 | 23 |
BS0100 | 26 |
Table B
Header 2 | Type |
---|---|
22 | Head |
2200 | Posting |
2210 | Posting |
23 | Head |
2300 | Posting |
2310 | Posting |
2320 | Posting |
26 | Head |
2600 | Posting |
2650 | Posting |
Result table:
Header 1 | Header 2 |
---|---|
BS0000 | 2200 |
BS0000 | 2210 |
BS0000 | 2300 |
BS0000 | 2310 |
BS0000 | 2320 |
BS0100 | 2600 |
BS0100 | 2650 |
The link are the values under "Header 2" in TableA, but they do not have to appear in result table.
The values under 22, 23 and 26 with "Posting" are the ones to appear.
Any help will be highly appreciated! Many thanks in advance.
t1:
LOAD
[Header 1],
[Header 2] as joinfield
FROM
[http://community.qlik.com/thread/115632]
(html, codepage is 1252, embedded labels, table is @1);
t2:
LOAD
[Header 2],
left([Header 2], 2) as joinfield,
Type
FROM
[http://community.qlik.com/thread/115632]
(html, codepage is 1252, embedded labels, table is @2)
where Type = 'Posting';
t1:
LOAD
[Header 1],
[Header 2] as joinfield
FROM
[http://community.qlik.com/thread/115632]
(html, codepage is 1252, embedded labels, table is @1);
t2:
LOAD
[Header 2],
left([Header 2], 2) as joinfield,
Type
FROM
[http://community.qlik.com/thread/115632]
(html, codepage is 1252, embedded labels, table is @2)
where Type = 'Posting';
Table1:
Load
[Header 1],
[Header 2] as Flag Inline
[
Header 1, Header 2
BS0000, 22
BS0000, 23
BS0100, 26
];
Join
Load
[Header 2],
Left([Header 2],2) as Flag,
Type
Inline
[
Header 2, Type
22, Head
2200, Posting
2210, Posting
23, Head
2300, Posting
2310, Posting
2320, Posting
26, Head
2600, Posting
2650, Posting
] Where Type <> 'Head';
Thank you both Massimo and Manish!
I think Massimo Grossi's answer is also correct and he has answered before me, so better if you select his answer as Correct and my one as Helpful.
This could be a true justice to his work also.
Hi Manish,
done as requested. You are totally right.
Sorry Massimo for my mistake, and thank you both again.