topic Transaction with different products in one line in QlikView

Transaction with different products in one line

Tue, 21 Apr 2015 09:29:51 GMT

Hi everyone,

I am working on a table of transactions (sales). Every row referes to on product that has been bought. The person can buy many products. My goal is to have only one line for each person and add columns for the products that has been bought, knowing that a person can buy no more than 10 products.

I don't know how to make that table in Qlikview starting with an Excel file with a line per product bought.

Thank you.

Re: Transaction with different products in one line

Tue, 21 Apr 2015 10:06:46 GMT

You can do row to column transpose... Post a sample file.

Re: Transaction with different products in one line

Tue, 21 Apr 2015 10:20:07 GMT

Re: Transaction with different products in one line

Gysbert_Wassenaar — Tue, 21 Apr 2015 10:29:23 GMT

Create an additional field in the script using the autonumber function:

Temp:

LOAD [Transaction ID], Product FROM ...source... ;

Result:

LOAD

[Transaction ID],

Product,

'Product ' & autonumber(RecNo(), [Transaction ID]) as ProductNo

RESIDENT Temp

ORDER BY [Transaction ID];

DROP TABLE Temp;

Create a pivot table with Transaction ID and ProductNo as dimensions and only(Product) as expression. Drag the ProductNo dimension to the right above the expression.

Re: Transaction with different products in one line

Tue, 21 Apr 2015 10:38:30 GMT

As a biginner in Qlikview, I didn't understand the part when I have to create a pivot table. I also didn't understand what is the meaning of this syntax.'Product ' & autonumber(RecNo(), [Transaction ID]) as ProductNo

thank you for your answer

Re: Transaction with different products in one line

Gysbert_Wassenaar — Tue, 21 Apr 2015 11:00:43 GMT

You could of course just try it out to see what it does, but the line 'Product ' & autonumber(RecNo(), [Transaction ID]) as ProductNo will create the values 'Product 1', 'Product 2', ... etc for each product value it encounters per Transaction ID value.

A pivot table is a chart object that you create in the front end after loading the data into Qlikview. Chart objects are used to show data and results from calculations so users can see and interact with it.

Re: Transaction with different products in one line

Tue, 21 Apr 2015 11:42:50 GMT

Thank you very much.

Re: Transaction with different products in one line

Tue, 21 Apr 2015 11:47:08 GMT

I tried the expression you gave me but it only gave me a blank table with no columns

Re: Transaction with different products in one line

Gysbert_Wassenaar — Tue, 21 Apr 2015 11:59:23 GMT

See attached example.

Re: Transaction with different products in one line

Tue, 21 Apr 2015 12:26:27 GMT

Hello, I do not see the attached example, can u please load it?

Re: Transaction with different products in one line

Wed, 22 Apr 2015 11:42:21 GMT

I achieved the result. However, it has been impossible for me to use the obtained table as it is an object. Could the result be obtained only using the script? Otherwise how can I use the pivot table in another object.

Best regards

Re: Transaction with different products in one line

Gysbert_Wassenaar — Wed, 22 Apr 2015 12:03:34 GMT

Temp1:

LOAD * INLINE [

Transaction ID, Product

1, B

2, D

2, F

2, E

1, A

2, A

];

Temp2:

GENERIC

LOAD

[Transaction ID],

'Product ' & autonumber(RecNo(), [Transaction ID]) as ProductNo,

Product

RESIDENT Temp1

ORDER BY [Transaction ID],Product;

DROP TABLE Temp1;

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

Let vTableName = TableName($(vTableNo)) ;

If Subfield(vTableName,'.',1)='Temp2' Then

Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

End If

Next vTableNo

Result:

Load FieldValue('Transaction ID', RecNo()) as [Transaction ID] AutoGenerate FieldValueCount('Transaction ID');

For each vTableName in $(vListOfTables)

Left Join (Result) Load * Resident [$(vTableName)];

Drop Table [$(vTableName)];

Next vTableName