Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
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)
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
Thanks Michael. Your suggestion was where my brain was going. I am going to test it today and see how it works.
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
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?