topic Re: (Another) Table transformation in QlikView

(Another) Table transformation

salto — Fri, 25 Apr 2014 13:20:58 GMT

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.

Re: (Another) Table transformation

maxgro — Fri, 25 Apr 2014 13:36:11 GMT

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

Re: (Another) Table transformation

MK_QSL — Fri, 25 Apr 2014 13:41:20 GMT

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

Re: (Another) Table transformation

salto — Fri, 25 Apr 2014 15:30:55 GMT

Thank you both Massimo and Manish!

Re: (Another) Table transformation

MK_QSL — Fri, 25 Apr 2014 15:32:33 GMT

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.

Re: (Another) Table transformation

salto — Fri, 25 Apr 2014 15:35:36 GMT

Hi Manish,

done as requested. You are totally right.

Sorry Massimo for my mistake, and thank you both again.