Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
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;

View solution in original post

Pedro_Rodriguez
Contributor III
Contributor III
Author

Thanks a lot Sunny! Works perfectly