topic Re: Help concatenating product purchases in QlikView

Help concatenating product purchases

mikkeltaylor — Sun, 17 Feb 2013 15:24:19 GMT

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.

Re: Help concatenating product purchases

Gysbert_Wassenaar — Sun, 17 Feb 2013 15:27:22 GMT

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

Re: Help concatenating product purchases

mikkeltaylor — Sun, 17 Feb 2013 16:46:24 GMT

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.

Re: Help concatenating product purchases

Gysbert_Wassenaar — Sun, 17 Feb 2013 18:01:29 GMT

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.

Re: Help concatenating product purchases

mikkeltaylor — Sun, 17 Feb 2013 18:41:51 GMT

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

Re: Help concatenating product purchases

mikkeltaylor — Mon, 18 Feb 2013 11:12:20 GMT

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!