Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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

Highlighted
Contributor III
Contributor III

Thanks a lot Sunny! Works perfectly