Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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 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)

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

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

sunny_talwar

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)


Gysbert_Wassenaar

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
Author

Hi,

with the data loaded inline, I made it work.

I attached the qvw, hope it helps.

Regards

Not applicable
Author

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