Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to get Result table from tables A and B:
Table A
Header 1 | Header 2 |
---|---|
BS0000 | 22 |
BS0000 | 231 |
BS0100 | 26 |
Table B:
Header 2 | Type |
---|---|
22 | Head |
231 | Head |
26 | Head |
2200 | Posting |
2210 | Posting |
23100 | Posting |
23110 | Posting |
23120 | Posting |
2600 | Posting |
2650 | Posting |
Result table:
Header 1 | Header 2 |
---|---|
BS0000 | 2200 |
BS0000 | 2210 |
BS0000 | 23100 |
BS0000 | 23110 |
BS0000 | 23120 |
BS0100 | 2600 |
BS0100 | 2650 |
Many thanks in advance!
T1:
LOAD [Header 1],
[Header 2] as Flag
FROM
[http://community.qlik.com/thread/115640]
(html, codepage is 1252, embedded labels, table is @1);
Join
T2:
LOAD IF(Len([Header 2])>4,Left([Header 2],3),Left([Header 2],2)) as Flag,
[Header 2],
Type
FROM
[http://community.qlik.com/thread/115640]
(html, codepage is 1252, embedded labels, table is @2)
Where Type <> 'Head';
T1:
LOAD [Header 1],
[Header 2] as Flag
FROM
[http://community.qlik.com/thread/115640]
(html, codepage is 1252, embedded labels, table is @1);
Join
T2:
LOAD IF(Len([Header 2])>4,Left([Header 2],3),Left([Header 2],2)) as Flag,
[Header 2],
Type
FROM
[http://community.qlik.com/thread/115640]
(html, codepage is 1252, embedded labels, table is @2)
Where Type <> 'Head';
LOAD [Header 1],
[Header 2] as H2
FROM
[http://community.qlik.com/thread/115640]
(html, codepage is 1252, embedded labels, table is @1);
left join
LOAD [Header 2],
Type,
left([Header 2],len([Header 2])-2) as H2
FROM
[http://community.qlik.com/thread/115640]
(html, codepage is 1252, embedded labels, table is @2)
where Type = 'Posting';
drop field H2,Type;
MapHeader:
Mapping LOAD [Header 2], [Header 1]&';'
FROM [http://community.qlik.com/thread/115640]
(html, codepage is 1252, embedded labels, table is @1);
tabResult:
LOAD SubField(Header1, ';', 1) as [Header 1],
[Header 2]
Where Len(SubField(Header1, ';', 2))>0;
LOAD MapSubString('MapHeader', [Header 2]) as Header1,
[Header 2]
FROM [http://community.qlik.com/thread/115640]
(html, codepage is 1252, embedded labels, table is @2);
regards
Marco
Hello Manish, Gysbert and Marco,
thank you all three - the three proposals work perfectly.
Best regards.