topic Re: First Sorted Value, set analysis and aggr.. it's aggravating! in QlikView

First Sorted Value, set analysis and aggr.. it's aggravating!

Fri, 08 May 2015 13:21:51 GMT

Simple enough task: Find the last call where the ActCode was "MDC" and the person who made it was the CSR assigned to the account. Then, find the last and second last call by the CSR that DOES NOT have 'MDC' as the ActCode. Here's a small sample of the data:(what I want for the first call is 5/6/15 10:05:59, and the second call is 5/5/15 9:10:34)

First attempt was this: =FirstSortedValue({$<[GMA.ActCode] -= {'MDC'} > } GMA.FullTime,-GMA.FullTime)

which works, except that it returns "5/6/15 16:26:34" , where the HistUser (the user who made the call) was not the CSR.

So, I tried =FirstSortedValue({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={GMA.HistUser}>} GMA.FullTime, -GMA.FullTime)

but that returns null.

So I tried =if(GMA.CSR=GMA.HistUser,FirstSortedValue({$<[GMA.ActCode] -= {'MDC'} > } GMA.FullTime,-GMA.FullTime))

but that returns null.

I tried =aggr(FirstSortedValue({$<[GMA.ActCode] -= {'MDC'} > } GMA.FullTime,-GMA.FullTime),GMA.CSR)

which returns 5/6/15 16:26:34 (and which is correct, since this is the last non-MDC call aggregated by CSR, but it doesn't give me what I want).

How do I specify: the last (and the second last) call made where the ActCode is NOT "MDC" and the HistUser=CSR?

thanks, Kevin

Re: First Sorted Value, set analysis and aggr.. it's aggravating!

Anonymous — Fri, 08 May 2015 13:30:32 GMT

You can try with Concat cat function with substring for 1st and second record

=SubField(Concat([Col],'@',1/[GroupCol]),'@',1)

Modify as required in your scenario

Thanks

BKC

Re: First Sorted Value, set analysis and aggr.. it's aggravating!

sunny_talwar — Fri, 08 May 2015 13:46:01 GMT

This expression is incorrect:

=FirstSortedValue({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={GMA.HistUser}>} GMA.FullTime, -GMA.FullTime)

Should be like this:

=FirstSortedValue({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={"=[GMA.CSR] = [GMA.HistUser]"}>} GMA.FullTime, -GMA.FullTime)

Re: First Sorted Value, set analysis and aggr.. it's aggravating!

Gysbert_Wassenaar — Fri, 08 May 2015 14:04:21 GMT

Should be like this:

=FirstSortedValue({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={"[GMA.CSR] = [GMA.HistUser]"}>} GMA.FullTime, -GMA.FullTime)

That's the same as
=Max({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={"[GMA.CSR] = [GMA.HistUser]"}>} GMA.FullTime)

And [GMA.CSR]={"[GMA.CSR] = [GMA.HistUser]"} may or may not give correct results. Judging from the screenshot I'd say it won't. See set_analysis_intra-record.qvw

Re: First Sorted Value, set analysis and aggr.. it's aggravating!

Fri, 08 May 2015 15:51:41 GMT

Hi,

with the data loaded inline, I made it work.

I attached the qvw, hope it helps.

Regards

Re: First Sorted Value, set analysis and aggr.. it's aggravating!

Fri, 08 May 2015 17:47:28 GMT

Thanks to everyone for their replies. In the end, I went back to the user, and found out they are not interested in the actions of anyone besides the assigned CSR (GMA.CSR), so I added to my script:

WHERE GMA.CSR=GMA.HISTUSER

and then was able to use

=aggr(FirstSortedValue({$<[GMA.ActCode] -= {'MDC'} > } GMA.FullTime,-GMA.FullTime),GMA.COMPANY)

Again, I thank everyone for their help, and especially Gysbert - his link edified some issues that had mystified me.

cheers,

Kevin