Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
salto
Specialist II
Specialist II

(The last I hope) table transformation

I would like to get Result table from tables A and B:

Table A

Header 1Header 2
BS000022
BS0000231
BS010026

Table B:

Header 2Type
22Head
231Head
26Head
2200Posting
2210Posting
23100Posting
23110Posting
23120Posting
2600Posting
2650

Posting

Result table:

Header 1Header 2
BS00002200
BS00002210
BS000023100
BS000023110
BS000023120
BS01002600
BS01002650

Many thanks in advance!

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

View solution in original post

4 Replies
MK_QSL
MVP
MVP

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;


talk is cheap, supply exceeds demand
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
Specialist II
Specialist II
Author

Hello Manish, Gysbert and Marco,

thank you all three - the three proposals work perfectly.

Best regards.