Data Table Transformation in Qliksense

[samuel_lin]

Hi Qlikers,

it's really hard to describe in the discussion subject what I am trying to do.

Here's a photo:

I have a table with two columns, one is OrderNumber and the other is Products.

I want to transform it to a table with distinct OrderNumber, and a list of products for that OrderNumber in the columns.

Is there a way to accomplish this in Qliksense? load scripts?

any idea?

Thanks,

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD *,

  AutoNumber(RowNo(), Order) as ProductNo;

LOAD * INLINE [

    Order, Product

    Order 101, Product A

    Order 101, Product D

    Order 101, Product K

    Order 102, Product D

    Order 102, Product G

    Order 102, Product Z

    Order 103, Product B

    Order 103, Product C

];

FinalTable:

LOAD Distinct Order

Resident Table;

FOR i = 1 to FieldValueCount('ProductNo')

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

  Left Join (FinalTable)

  LOAD Order,

  Product as [Product $(vField)]

  Resident Table

  Where ProductNo = $(vField);

NEXT

DROP Table Table;

[samuel_lin]

Hi Sunny, this is great!! Thank you so much.

Before I close this discussion, do you know if there a way in Qliksense that I can just download this entire Final table? instead of adding all the fields as dimensions?

Thanks,

Samuel

[sunny_talwar]

Download? As in create a qvd?

[samuel_lin]

as download the data as csv from a specific table.

[sunny_talwar]

You can store this data in a csv through the script itself

Store ‒ Qlik Sense

[samuel_lin]

Thank you so much!

[sunny_talwar]

No problem at all

[Anonymous]

Dear Sunny,

What if I have multiple values to identify unique order?

How do I derive the same table with Customer & Order as a combined key?

Example:

LOAD * INLINE [

    Customer, Order, Product

    Customer A, Order 101, Product A

    Customer A, Order 101, Product D

    Customer A, Order 101, Product K

    Customer B, Order 101, Product A

    Customer C, Order 102, Product D

    Customer C, Order 102, Product G

    Customer C, Order 102, Product Z

    Customer D, Order 103, Product B

    Customer D, Order 103, Product C

    Customer E, Order 103, Product B

    Customer E, Order 103, Product C

];

[sunny_talwar]

I am not completely sure I understand, but may be this:

AutoNumber(RowNo(), Customer&Order) as ProductNo;