Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Max Value based on a date

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

Re: Max Value based on a 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 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

qlikrajan
Contributor III

Re: Max Value based on a date

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

Re: Max Value based on a date

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

Re: Max Value based on a date

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

Not applicable

Re: Max Value based on a date

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

Re: Max Value based on a date

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?

Community Browser