Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

mikkeltaylor
Contributor II

Help concatenating product purchases

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 ProductPurchase Date
1A1/1/2010
1B1/1/2011
1C1/1/2012
2C1/1/2010
2A1/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.

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Help concatenating product purchases

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.


talk is cheap, supply exceeds demand
5 Replies
MVP & Luminary
MVP & Luminary

Re: Help concatenating product purchases

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


talk is cheap, supply exceeds demand
mikkeltaylor
Contributor II

Re: Help concatenating product purchases

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Help concatenating product purchases

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.


talk is cheap, supply exceeds demand
mikkeltaylor
Contributor II

Re: Help concatenating product purchases

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
Contributor II

Re: Help concatenating product purchases

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!