Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Pedro_Rodriguez
New Contributor III

Undo Crosstable

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:

 

TypePckProvid1Provid2Provid3
1pck11prv111prv112
1pck12prv122
2pck21prv211
3pck31prv311prv312prv313

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

Tags (3)
1 Solution

Accepted Solutions

Re: Undo Crosstable

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;

2 Replies

Re: Undo Crosstable

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;

Pedro_Rodriguez
New Contributor III

Re: Undo Crosstable

Thanks a lot Sunny! Works perfectly