topic Re: FirstSortedValue with conditions in QlikView

FirstSortedValue with conditions

Mon, 17 Aug 2015 19:46:59 GMT

A db I work with stores its notes in a compressed format. Decompressing is very CPU intensive, so I have stored all the notes in a .qvd file (after decompressing). The QVD file stores, among other things, the ActivityRep (whoever entered the note in the db), the FullTime the note was entered (down to tenths of a second), and the Note as a text field.

My users want to see the latest note where the note taker was the CSR assigned to the company. I have a straight table with dimensions Company, CSR, forecast$, etc. One of my expressions is

if(HistUser = CSR, FirstSortedValue(FullTime,-FullTime)) <- this works fine to give me the last date the CSR called the client

However, now I want to put the note in the chart as well. I tried:

if(NoteTakerID = CSR, FirstSortedValue(Notes, -FullTime)) <- always shows null, even if there are notes that meet the criteria

When I read the above, I think it reads "If the NoteTakerID is the same as the CSR ID in this row, find the most recent note".

In my tables, CSR and Company are in one table; NoteTakerID, Notes, and FullTime are in a different table. Is there a way to do this using AGGR()?

Re: FirstSortedValue with conditions

sinanozdemir — Mon, 17 Aug 2015 19:53:13 GMT

Hi Kevin,

Sample data might be very useful in your case. Can you post some sample data if it is not confidential or dummy your data set to something similar to your original data set?

Re: FirstSortedValue with conditions

swuehl — Mon, 17 Aug 2015 19:55:52 GMT

Try

FirstSortedValue( if(NoteTakerID = CSR,Notes), -FullTime)

Re: FirstSortedValue with conditions

Tue, 18 Aug 2015 16:17:31 GMT

Hi swuehl,

Tried that already, still gives me a null result.

"Activity_rep" is the same field as "NoteTakerID".

I tried Set Analysis as well: FirstSortedValue({$<[Activity_rep]={CSR}>} Notes, -FullTime), but that didn't work either.

Re: FirstSortedValue with conditions

swuehl — Tue, 18 Aug 2015 21:30:05 GMT

Since set analysis is evaluated once per chart, not per dimension value, {$<[Activity_rep]={CSR}>} will not work with CSR being your dimension, unless the chart is limited to a single CSR value.

As Sinan already stated, some sample data lines that show the structure of your tables would really be helpful, it's quite unclear how your data and tables look like in detail.

Since FirstSortedValue() will show NULL when more than one expression values share the same lowest sort order, you can add DISTINCT qualifier to return a value in that case, like

FirstSortedValue(DISTINCT {$<[Activity_rep]={CSR}>} Notes, -FullTime)

But I think key to your issue is to understand how your tables are linked and I seem to haven't understood that really.

Would be best if you could provide a small sample QVW (could contain mock up data values).

Re: FirstSortedValue with conditions

rwunderlich — Tue, 18 Aug 2015 23:57:51 GMT

Perhaps...

FirstSortedValue({<Notes={"NoteTakerID = CSR"}>}DISTINCT Notes, -FullTime))

-Rob