Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max Value based on a date

Hi,

I know the answer to this is simple and I am just having a brain-fart. I have a table with site names, dates, and scores. For each site name, I need to pull the scores based on the latest date.

Site NameDateProcessProcess A ScoreProcess B Score
Boston1/10/15A4
Boston9/8/14B4
Chicago11/11/14B5
Chicago8/1/14A,B24
Chicago2/2/14B2
Memphis11/1/14A4
New York12/6/14B3
New York8/5/14A,B44
Philadelphia1/1/15A4
Philadelphia10/10/14B4
Philadelphia4/1/14A,B33

I want to create a straight table that shows the most recent score based upon the process. In the example below, last time we went to Philadelphia and looked at Process A was 1/1/15 and the score was 4. Last time we went to Philadelphia and looked at Process B was 10/10/14 and the score was 4.

Site NameProcess A ScoreProcess B Score
Boston44
Chicago25
Memphis4
New York43
Philadelphia44

I have tired the FirstSortedValue() function but end up getting null values for certain sites and I cannot figure out why. It's driving me crazy!

Thanks in advance,

Drew

7 Replies
Not applicable
Author

I don't know how you are planning to handle Process A,B case but try adding set analysis to your formula.

For A

FirstSortedValue({$ <Process = {'A'}> }[Process A Score],-Date)

For B

FirstSortedValue({$ <Process = {'B'}> }[Process B Score],-Date)

Not applicable
Author

Hi Drew,

To use Erdal's solution you need to load your table in such a format where 'Process' field doesn't have any comma separated values. Add additional records for second 'Process' values. such like shown below:

                                                                                                                                          

Site NameDateProcessProcess A ScoreProcess B Score
Boston1/10/2015A4
Boston9/8/2014B 4
Chicago11/11/2014B 5
Chicago8/1/2014A2
Chicago8/1/2014B 4
Chicago2/2/2014B 2
Memphis11/1/2014A4
New York12/6/2014B 3
New York8/5/2014A44
New York8/5/2014B 4
Philadelphia1/1/2015A4
Philadelphia10/10/2014B 4
Philadelphia4/1/2014A3
Philadelphia4/1/2014B 3

You can do concatenate load of the same table two times like:

Load SiteName, Date, Left(Process,1), [Process A Score], if(index(Process,','),null(),[Process B Score]) as [Process B Score]

from <TableName>;

concatenate

Load

SiteName, Date, Right(Process,1), '' as [Process A Score], [Process B Score],

from <TableName> where index(Process,',')>=1;

There are other methods also to create above table like load A process once and then load B process etc. (Depends upon your requirement).

One you get this table structure you can use Erdal's formula's in expression to get the desired results.

Hope this helps,

Anosh

Anonymous
Not applicable
Author

Please find the solution attached. You will get null only for Memphis because you don.t have any Process B score  for Memphis.

FirstSortedValue({ <Process = {'A*'}> }[Process A Score],-DateA)

maternmi
Creator II
Creator II

Hi,

I would you recommend you to generate two field (for Process A & B score) which already contains the scores based on the latest date. You can do it as follows

Temptabale:

load ID,

firstsortedvalue([Process A score],-Date) as Process_A_score_latest,

firstsortedvalue([Process B score],-Date) as Process_B_score_latest,

from

\\Your.qvd (qvd)

group by

ID

The generated fields you can join to the relevant table.


BR

Michael

Not applicable
Author

Thanks Michael. Your suggestion was where my brain was going. I am going to test it today and see how it works.

Not applicable
Author

Hi Drew,

If you want to keep the final structure with site having process score in your datamodel and link it further with other tables, than it is a good idea to do it in datamodel otherwise i would suggest you to either go with Qlikranjan or my explained solution (depends upon the table structure you would like to keep in datamodel).

You should incorporate business logic in data load script only when

1. You would like to keep the output (eg Aggregated table)  in datamodel and don't want the source data

2. There is no possible way to achieve it in UI other than doing it in load script;

if you keep only the aggregated table in datamodel, you would be eliminating the future need of source data.

if you keep both source and aggregated table in datamodel, you would be replicating the data in datamodel, which would increase the load time, size of app and memory footprint on server.

Hope this help,

Anosh

Not applicable
Author

Hi Michael,

Using your solution, I still get null values.

   

SiteProcess AProcess BDate
Philadelphia441/1/2015
Philadelphia310/10/2014
Philadelphia34/1/2014

With the above, I would expect to get

Philadelphia 4 4

However, I get the below:

  

SiteProcess A Score (Latest)Process B Score (Latest)
Philadelphia--

Any idea what causes the null values?