Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
samuel_lin
Contributor

Table Transformation - Customer Journey (Products)

Hi Qlikers,

If I have a table:

CustomerIDOrderRankPurchased Product
1231productA
...1productB
...2productA
...2productK
1233productJ

and I would like to get the following table:

CustomerIDFirst Purchased ProductSecond Purchased ProductThird Purchased Product
123productAproductAproductJ
...productAproductKproductJ
...productBproductAproductJ
...productBproductKproductJ

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?

Thank you!

Samuel

2 Replies
bwisealiahmad
Valued Contributor

Re: Table Transformation - Customer Journey (Products)

Hi,

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:

Temp_Numbers:

LOAD * INLINE [

    CustomerID, OrderRank, PurchasedProduct

    123, 1, ProductA

    123, 1, ProductB

    123, 2, ProductA

    123, 2, ProductK

    123, 3, ProductJ

];

Facts:

// First Products //

NoConcatenate

LOAD

CustomerID,

PurchasedProduct AS FirstPurchasedProduct

Resident Temp_Numbers

WHERE OrderRank = '1';

// Second Products //

Left Join (Facts)

LOAD

CustomerID,

PurchasedProduct AS SecondPurchasedProduct


Resident Temp_Numbers

WHERE OrderRank = '2';

// Third Products //


Left Join (Facts)


LOAD

CustomerID,

PurchasedProduct AS ThirdPurchasedProduct

Resident Temp_Numbers

WHERE OrderRank = '3';


Drop Table Temp_Numbers;


That gives you this:

Capture.PNG

Best,

Ali A

MVP & Luminary
MVP & Luminary

Re: Table Transformation - Customer Journey (Products)

I think you can do it with a single Generic load statement:

Generic

LOAD CustomerID,

    'Purchased Product #' & OrderRank,

    [Purchased Product]

FROM ....

See Use cases for Generic Load | Qlikview Cookbook for a similar example.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com