# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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:

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

## Re: Data Table Transformation in Qliksense

May be this:

Table:

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:

Resident Table;

FOR i = 1 to FieldValueCount('ProductNo')

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

Left Join (FinalTable)

Product as [Product \$(vField)]

Resident Table

Where ProductNo = \$(vField);

NEXT

DROP Table Table;

10 Replies
MVP

## Re: Data Table Transformation in Qliksense

May be this:

Table:

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:

Resident Table;

FOR i = 1 to FieldValueCount('ProductNo')

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

Left Join (FinalTable)

Product as [Product \$(vField)]

Resident Table

Where ProductNo = \$(vField);

NEXT

DROP Table Table;

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

Contributor

## Re: Data Table Transformation in Qliksense

as download the data as csv from a specific table.

MVP

## Re: Data Table Transformation in Qliksense

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

Store ‒ Qlik Sense

Contributor

## Re: Data Table Transformation in Qliksense

Thank you so much!

MVP

## Re: Data Table Transformation in Qliksense

No problem at all

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

## Re: Data Table Transformation in Qliksense

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

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