Help concatenating product purchases

[mikkeltaylor]

Hi,

I am fairly new to Qlikview and struggling to come up with the correct expression.

I am looking to calculate all product purchases for each customer into one string based on the order the products were purchased. i.e. A | B | C.

CustomerID	Product	Purchase Date
1	A	1/1/2010
1	B	1/1/2011
1	C	1/1/2012
2	C	1/1/2010
2	A	1/1/2012

It needs to be dynamic, which means it will update as and when selections are made relating to the product table.

Many thanks in advance for any help.

Michael.

[Gysbert_Wassenaar]

Add concatenate(Product, ' | ',[Purchase Date]) as expression to your chart and CustomerID as dimension.

[mikkeltaylor]

Hi Gysbert, thanks for the reply.

To add further context, I am looking to create a chart that shows the most common product purchases, which would mean I would need to order the products by purchase date for each customer and then aggregate up to show a count of customers by the newly defined field.

How would I need to accomplish this?

Thanks.

[Gysbert_Wassenaar · Accepted solution]

I probably don't understand what you want, because I don't see how something like that could be of any use. Only if more than one customer buys exactly the same products in exactly the same order will you get any other count than 1. See attached qvw. Seems like an exercise in futility to me to be honest. If you're trying to do what's often called basket analysis try searching the forums and see here for some links.

[mikkeltaylor]

I work for an insurance company.  We sell prob about a dozen different types of policies, but most customers will only hold a few so it's not really transaction based like market basket analysis.

I'll check the file you've attached when I get into work tomorrow and hopefully it will do the trick

Thanks

[mikkeltaylor]

It worked! I haven't used concat before and was expecting to have to apply a nested ranking for the order of purchase date, but it was even simpler than expected.

Thanks for your help!