(The last I hope) table transformation

[salto]

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!

[MK_QSL · Accepted solution]

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';

[Gysbert_Wassenaar]

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;

[MarcoWedel]

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

[salto]

Hello Manish, Gysbert and Marco,

thank you all three - the three proposals work perfectly.

Best regards.