Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

samuel_lin
Contributor

Data Table Transformation in Qliksense

Hi Qlikers,

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

Here's a photo:

IMG_20170315_130656.jpg

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,

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Data Table Transformation in Qliksense

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;

10 Replies
MVP
MVP

Re: Data Table Transformation in Qliksense

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
Contributor

Re: Data Table Transformation in Qliksense

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

MVP
MVP

Re: Data Table Transformation in Qliksense

Download? As in create a qvd?

samuel_lin
Contributor

Re: Data Table Transformation in Qliksense

as download the data as csv from a specific table.

MVP
MVP

Re: Data Table Transformation in Qliksense

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

Store ‒ Qlik Sense

samuel_lin
Contributor

Re: Data Table Transformation in Qliksense

Thank you so much!

MVP
MVP

Re: Data Table Transformation in Qliksense

No problem at all

kchuying
New Contributor II

Re: Data Table Transformation in Qliksense

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

];

MVP
MVP

Re: Data Table Transformation in Qliksense

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

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