Max Value based on a date

[#1]

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 Name	Date	Process	Process A Score	Process B Score
Boston	1/10/15	A	4
Boston	9/8/14	B		4
Chicago	11/11/14	B		5
Chicago	8/1/14	A,B	2	4
Chicago	2/2/14	B		2
Memphis	11/1/14	A	4
New York	12/6/14	B		3
New York	8/5/14	A,B	4	4
Philadelphia	1/1/15	A	4
Philadelphia	10/10/14	B		4
Philadelphia	4/1/14	A,B	3	3

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 Name	Process A Score	Process B Score
Boston	4	4
Chicago	2	5
Memphis	4
New York	4	3
Philadelphia	4	4

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

[#2]

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)

[#3]

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 Name	Date	Process	Process A Score	Process B Score
Boston	1/10/2015	A	4
Boston	9/8/2014	B		4
Chicago	11/11/2014	B		5
Chicago	8/1/2014	A	2
Chicago	8/1/2014	B		4
Chicago	2/2/2014	B		2
Memphis	11/1/2014	A	4
New York	12/6/2014	B		3
New York	8/5/2014	A	4	4
New York	8/5/2014	B		4
Philadelphia	1/1/2015	A	4
Philadelphia	10/10/2014	B		4
Philadelphia	4/1/2014	A	3
Philadelphia	4/1/2014	B		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]

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]

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

[#6]

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

[#7]

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

[#8]

Hi Michael,

Using your solution, I still get null values.

Site	Process A	Process B	Date
Philadelphia	4	4	1/1/2015
Philadelphia		3	10/10/2014
Philadelphia	3		4/1/2014

With the above, I would expect to get

Philadelphia 4 4

However, I get the below:

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

Any idea what causes the null values?