topic Re: Undo Crosstable in QlikView

Undo Crosstable

Pedro_Rodriguez — Mon, 16 Jul 2018 14:10:27 GMT

Hi everyone,

I start from a table like this:

load * INLINE [

Type, Pck, Provid

1, pck11, prv111

1, pck11, prv112

1, pck12, prv122

2, pck21, prv211

3, pck31, prv311

3, pck31, prv312

3, pck31, prv313

];

What I want to get it's something like:

Type	Pck	Provid1	Provid2	Provid3
1	pck11	prv111	prv112
1	pck12	prv122
2	pck21	prv211
3	pck31	prv311	prv312	prv313

Basically is creating a record per each pair Type-Pck and populate the remainig columns with its providers

Bear in mind that the maximun number of providers is not fixed.

Can you guys help me?

Thanks a lot in advance

Re: Undo Crosstable

sunny_talwar — Mon, 16 Jul 2018 14:46:43 GMT

Try this

Table:

LOAD *,

AutoNumber(RowNo(), Pck) as Number;

LOAD * INLINE [

Type, Pck, Provid

1, pck11, prv111

1, pck11, prv112

1, pck12, prv122

2, pck21, prv211

3, pck31, prv311

3, pck31, prv312

3, pck31, prv313

];

FinalTable:

LOAD DISTINCT Type,

Pck

Resident Table;

FOR i = 1 to FieldValueCount('Number')

LET vField = FieldValue('Number', $(i));

Left Join (FinalTable)

LOAD DISTINCT Type,

Pck,

Provid as [Provid$(vField)]

Resident Table

Where Number = $(vField);

NEXT

DROP Table Table;

Re: Undo Crosstable

Pedro_Rodriguez — Tue, 17 Jul 2018 06:23:03 GMT

Thanks a lot Sunny! Works perfectly