Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Pedro_Rodriguez
Contributor III
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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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
Contributor III
Contributor III
Author

Thanks a lot Sunny! Works perfectly