How to obtain the latest date from exisiting dataset

[#1]

Hi all,

I had a table as below:

Forecast_Type	Forecast_Date	Forecast_Amt	Forecast_run_date
Inward	01/03/2014	100	22/02/2014
Inward	01/03/2014	102	23/02/2014
Inward	01/03/2014	104	24/02/2014
Inward	01/03/2014	106	25/02/2014
Inward	01/03/2014	108	26/02/2014
Inward	02/03/2014	200	23/02/2014
Inward	02/03/2014	202	24/02/2014
Inward	02/03/2014	204	25/02/2014
Inward	02/03/2014	206	26/02/2014
Inward	02/03/2014	208	27/02/2014
Inward	03/03/2014	300	24/02/2014
Inward	03/03/2014	302	25/02/2014
Inward	03/03/2014	304	26/02/2014
Inward	03/03/2014	306	27/02/2014
Inward	03/03/2014	308	28/02/2014

Let's say if I want to obtain the Forecast_Amt for the Forecast_Date based on the latest Forecast_run_date, can anyone advice how should I go about writing the expression in the straight table for that? I tried using the expression

=if(Forecast_Type='Inward' and max(Forecast_run_Date),sum(Forecast_Amt))

but it doesn't work. An example of output which I want to have when Forecast_Date is 01/03/2014 is 108.@@

[paul_scotchford]

Hi

Load your table as normal but add a flag e.g. 1 as fTxn (for every row in the load) then

the below variable assignment will show the latest ... you can build on this example ...

vMaxDataDate =date(max(if(fTxn=1,Forecast_run_date)))

Then sum via set analysis ... (we do this type of sum on dates all the time here)

sum({<Forecast_run_date, Forecast_Type, Forecast_Type={'Inward'}, Forecast_run_date={'=$(vMaxDataDate )'}>} Forecast_Amount)

[#3]

Hi Paul,

Thanks for your reply. May I check with you, when you mention about adding a flag for every row in the load, do you mean that I had to add it inside my dataset manually or I just need to write an expression to add the flag to add in into the dataset? Thanks!

[paul_scotchford]

Hi

Yes you load script is where its done, we load from QVD's , hence if you did the same your

load would look like the below format..

fact:

LOAD 

    Date as FactDate

field1 .

field 2.

. and so on

  , 1 as fTxn

FROM

   QVD\fact.qvd

if you do directly from SQL to QVD or QVW you could just add the line to your sql ...

select

     field 1, 2, 3 etc

     1 as fTxn

from schema.table

Get the gist of it ?

cheers

[#5]

Hi Paul,

I tried to use the flag to load, but there is a problem, when I tried to define

vMaxDataDate =date(max(if(fTxn=1,Forecast_run_date)))

and load the script but there is an error in scripts message shown. I am wondering if you are able to show me  an example based on my table above? Many thanks!

[paul_scotchford]

The vMaxDataDate is a variable so you can define that anywhere either manually in the Variables browser or

in the Loader.

can you post your load script ?

[#7]

  Here it is

vMaxDataDate =date(max(if(fTxn=1,Forecast_run_date)))

LOAD

Forecast_Type,
Forecast_Date,
Forecast_Amt,
Forecast_run_date,

1 as fTxn

FROM

C:\......\TEST20MAR.xls
(biff, embedded labels , table is [Sheet1$]);

Thank you

[rwunderlich · Accepted solution]

In a Straight Table with Dimension of Forecast_Date I would use the expression:

=FirstSortedValue(Forecast_Amt, -Forecast_run_date)

-Rob

http://masterssummit.com

http://robwunderlich.com

[#9]

Hi Rob,

Many thanks! It works!!