topic Re: Re: FirstSortedValue issue in QlikView

FirstSortedValue issue

Thu, 21 Aug 2014 10:41:15 GMT

Hi everyone! I would like to get the first product sold to each customer , based on the date of the transaction. The problem is that when more than one products have been sold to a customer on the same date, function FirstSortedValue(ItemCode,DateT) returns null. In that case, I would like to get all the products.

Any ideas?

Re: FirstSortedValue issue

Gysbert_Wassenaar — Thu, 21 Aug 2014 11:21:16 GMT

Something like: FirstSortedValue(aggr(concat(ItemCode,', ')DateT,CustomerID),DateT)

Re: FirstSortedValue issue

Thu, 21 Aug 2014 11:49:04 GMT

Unfortunately, this doesn't work....

Re: FirstSortedValue issue

Gysbert_Wassenaar — Thu, 21 Aug 2014 12:00:04 GMT

Please post an example document.

Re: Re: FirstSortedValue issue

Thu, 21 Aug 2014 12:07:56 GMT

As you can see from the attachment, FirstSortedValue doesn't show records for customer James as he bought 2 products the first date (day 2). I would like to get two records, showing both products for customer James.

Thank you in advance for your help,

Re: FirstSortedValue issue

Gysbert_Wassenaar — Thu, 21 Aug 2014 12:22:01 GMT

FirstSortedValue(aggr(Concat(ItemCode,'|'),CustomerCode,DateT),aggr(DateT,DateT,CustomerCode))

Re: Re: FirstSortedValue issue

Thu, 21 Aug 2014 12:43:06 GMT

Thank you Gysbert, but what I would like is for Customer James to get two records instead of concatenated the two product codes on one record. The reason for that is that I want to count how many customers bought each product for the first time. Hope I was descriptive enough 🙂

Re: Re: FirstSortedValue issue

Gysbert_Wassenaar — Thu, 21 Aug 2014 12:49:46 GMT

That cannot be done with expressions alone. You'll have add some things in the script. Join the original table with:

join(Original)

Load CustomerCode, min(DateT) as DateT, 1 as Flag

resident Original

Group By CustomerCode;

Then you can use as expression only({<Flag={1}>}ItemCode)

Re: Re: FirstSortedValue issue

Thu, 21 Aug 2014 13:30:03 GMT

Thank you very much!