Creation Expression Colums and Data from Dimension Values

[JulesVerne]

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

[QFabian · Accepted solution]

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

[JulesVerne]

Thank you Dear @QFabian 

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