topic Table transformation in QlikView

Table transformation

salto — Fri, 25 Apr 2014 07:14:07 GMT

Hello Community,

I would like to transform this data table:

Header 1	Header 2
BS001	BS002
BS001	BS003
BS002	BS004
BS002	246
BS003	BS005
BS003	BS006
BS005	247
BS005	248
BS004	249
BS004	250
BS006	251
BS006	252
BS006	253

onto this one:

Header 1	Header 2
BS001	249
BS001	250
BS001	246
BS001	247
BS001	248
BS001	251
BS001	252
BS001	253
BS002	249
BS002	250
BS002	246
BS003	247
BS003	248
BS003	251
BS003	252
BS003	253
BS004	249
BS003	250
BS005	247
BS005	248
BS006	251
BS006	252
BS006	253

Any help will be highly appreciated,

Re: Table transformation

jagan — Fri, 25 Apr 2014 07:21:57 GMT

Hi,

Try like this

Data:

LOAD DISTINCT

[Header 1]

FROM

[http://community.qlik.com/thread/115573]

(html, codepage is 1252, embedded labels, table is @1);

LEFT JOIN

Data:

LOAD DISTINCT

[Header 2]

FROM

[http://community.qlik.com/thread/115573]

(html, codepage is 1252, embedded labels, table is @1)

WHERE IsNum([Header 2]);

Regards,

Jagan.

Re: Table transformation

salto — Fri, 25 Apr 2014 10:42:06 GMT

Hello Jagan,

sorry, I marked your answer as correct but unfortunately it is not.

Your proposal concatenates all Header2 to Header1, but the goal is to concatenate only those Header2 that "depend" from Header1.

For example, BS006 only has 251, 252 and 253.

How could I do this?

Thanks

Re: Table transformation

salto — Fri, 25 Apr 2014 11:33:17 GMT

Hi,

I found a solution very very near to what I need, via HierarchyBelongs to:

AccSched:

load

[Header 1] as RowNo,

'Desc' as DescriptionID,

[Header 2]as GLAccount

//SubField(Totaling, '+') as GLAccount,

//[Totaling Type] as TotalingType2

from

[http://community.qlik.com/thread/115573]

(html, codepage is 1252, embedded labels, table is @1);

BomAccSchedTmp:

HierarchyBelongsTo (GLAccount, RowNo, DescriptionID, AncestorID, AncestorName, Depth)

LOAD

GLAccount,

RowNo,

DescriptionID

RESIDENT AccSched;

noconcatenate

BomAccSched:

LOAD * resident BomAccSchedTmp where Depth >0;

drop table BomAccSchedTmp;

Regards!