Discussion board where members can get started with Qlik Sense.
If I have a table:
and I would like to get the following table:
so then in aggregate, i can look at the number of customers who purchased from productA to productA to productJ, basically to figure out the most popular customer purchasing path.
Does anyone know an easy way to achieve this table transformation?
Hope this helps. You could actually maybe do it even more more smooth with a For Each Loop, but I'm off to bed so hopefully this helps:
Put this in your script:
LOAD * INLINE [
CustomerID, OrderRank, PurchasedProduct
123, 1, ProductA
123, 1, ProductB
123, 2, ProductA
123, 2, ProductK
123, 3, ProductJ
// First Products //
PurchasedProduct AS FirstPurchasedProduct
WHERE OrderRank = '1';
// Second Products //
Left Join (Facts)
PurchasedProduct AS SecondPurchasedProduct
WHERE OrderRank = '2';
// Third Products //
PurchasedProduct AS ThirdPurchasedProduct
WHERE OrderRank = '3';
Drop Table Temp_Numbers;
That gives you this:
I think you can do it with a single Generic load statement:
'Purchased Product #' & OrderRank,
See Use cases for Generic Load | Qlikview Cookbook for a similar example.