Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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 dataSmiley Sadwhat I want for the first call is 5/6/15 10:05:59, and the second call is 5/5/15 9:10:34)

mdc.PNG

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

5 Replies
balkumarchandel
Valued Contributor II

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

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!

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!

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



talk is cheap, supply exceeds demand
Not applicable

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

Hi,

with the data loaded inline, I made it work.

I attached the qvw, hope it helps.

Regards

Not applicable

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

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

Community Browser