topic Re: Creation Expression Colums and Data from Dimension Values in QlikView

Creation Expression Colums and Data from Dimension Values

JulesVerne — Sun, 27 Mar 2022 19:26:59 GMT

Dear Experts,

I couldnt gave the exact subject for this question.

What I Want :
I want to create sales-analysis data table from shopping receipts.

ProductCategory is N Type...
Datas come from SQL from related views..

I have shopping list for each customer in SQL data like :

CustomerID	OrderNo	ProductCategory	SalesQty	....
7	1	Dress	10
7	1	Tunic	43
7	1	Skirt	22
7	1	T-Shirt	70
3	2	Tunic	22
3	2	Trousers	57
7	3	Legging	39
7	3	Dress	12

I need bolow Cart :

If any of product category in the order Write "1"

OrderNo	Dress	Tunic	Skirt	T-Shirt	Tunic	Trousers	Legging
1	1	1	1	1	0	0	0
2	0	0	0	0	1	1	0
3	1	0	0	0	0	0	0

I tries some SET Analysis formula.. But i couldn solve.

Please help me..

Regards

Re: Creation Expression Colums and Data from Dimension Values

QFabian — Mon, 28 Mar 2022 21:26:36 GMT

Hi @JulesVerne , you can try with a pivot table , here an example :

Data:
LOAD * INLINE [
CustomerID, OrderNo, ProductCategory, SalesQty
7, 1, Dress, 10
7, 1, Tunic, 43
7, 1, Skirt, 22
7, 1, T-Shirt, 70
3, 2, Tunic, 22
3, 2, Trousers, 57
7, 3, Legging, 39
7, 3, Dress, 12
];

pivot Chart, with two dimensions and one expression

count(OrderNo)

QVW attached below

Re: Creation Expression Colums and Data from Dimension Values

JulesVerne — Tue, 30 May 2023 09:28:18 GMT

Thank you Dear @QFabian

Sorry for late reply... This is very creative solution🙏