Table Transformation - Customer Journey (Products)

[samuel_lin]

Hi Qlikers,

If I have a table:

CustomerID	OrderRank	Purchased Product
123	1	productA
...	1	productB
...	2	productA
...	2	productK
123	3	productJ

and I would like to get the following table:

CustomerID	First Purchased Product	Second Purchased Product	Third Purchased Product
123	productA	productA	productJ
...	productA	productK	productJ
...	productB	productA	productJ
...	productB	productK	productJ

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

[bwisealiahmad]

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:

Best,

Ali A

[rwunderlich]

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