Fetch records wrt latest date

[ashish_2511]

Dear community -

Here's the issue I'm facing -

  I/P Table :-  

DEPOT_CD	PART_NO	STOCK_QTY	UPD_DT_TM
S1	0000001899	0	9/30/2015 0:02
S1	0000001899	0	4/1/2016 0:12
S1	0000001899	1	10/1/2013 0:02
S1	0000001899	1	4/1/2014 0:11
S1	0000001899	1	4/1/2015 0:11
S1	0000001899	2	6/1/2013 19:17

Each part # here can have different stock values wrt date. In my resulting table I want to show the the stock quantity of a part wrt to the latest date. Hence the O/p table should look like below -

DEPOT_CD	PART_NO	STOCK_QTY	UPD_DT_TM
S1	0000001899	0	4/1/2016 0:12

Please suggest the best way to achieve this in the load script.

Thanks in advance

Sneh

[zhadrakas]

You could use a presceding load like this:

Just put the following code above your load script for the I/P Table:

I/P Table:

Load

DEPOT_CD,

PART_NO,

sum(STOCK_QTY) as STOCK_QTY,

max(UPD_DT_TM) as UPD_DT_TM

Group by DEPOT_CD, PART_NO

;

//your current load script

[Anonymous]

May be ,this will help you.

Stock qty

=only({<UPD_DT_TM={'$(=max(UPD_DT_TM))'}>}STOCK_QTY)

date

max(UPD_DT_TM)

Thanks

Paridhi

[maxgro · Accepted solution]

[I/P Table]:

LOAD

  DEPOT_CD,

    PART_NO,

    STOCK_QTY,

    Timestamp(Timestamp#(UPD_DT_TM, 'M/D/YYYY h:m')) as UPD_DT_TM

FROM

[https://community.qlik.com/thread/254842]

(html, codepage is 1252, embedded labels, table is @1);

Left Join ([I/P Table])

LOAD

  DEPOT_CD,

    PART_NO,

    1 as FlagLatest,

    Timestamp(Max(UPD_DT_TM)) as UPD_DT_TM

Resident

  [I/P Table]

Group By

  DEPOT_CD,

    PART_NO;

[Anonymous]

Hi Snehasis ,

PFA the qvw for reference.

I have created a Flag called Latest Flag where in 1 represents latest and 0 represents old.

Thus, if you want a separate table for Latest Dates, create another table with a where condition of Latest Flag =1.

Do let me know for any concern or doubt.

Regards

KP

[yoganantha321]

May be the below link is useful:

Get Max Date and Max Value of a ID