Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

salto
Valued Contributor 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!

Tags (1)
1 Solution

Accepted Solutions

Re: (The last I hope) table transformation

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

4 Replies

Re: (The last I hope) table transformation

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

Re: (The last I hope) table transformation

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

Re: (The last I hope) table transformation

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
Valued Contributor II

Re: (The last I hope) table transformation

Hello Manish, Gysbert and Marco,

thank you all three - the three proposals work perfectly.

Best regards.

Community Browser