topic Re: Fetch records wrt latest date in QlikView

Fetch records wrt latest date

ashish_2511 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Fetch records wrt latest date

zhadrakas — Mon, 27 Mar 2017 09:39:53 GMT

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

Re: Fetch records wrt latest date

Anonymous — Mon, 27 Mar 2017 09:44:22 GMT

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

Re: Fetch records wrt latest date

maxgro — Mon, 27 Mar 2017 09:53:36 GMT

[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;

Re: Fetch records wrt latest date

Anonymous — Mon, 27 Mar 2017 10:07:56 GMT

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

Re: Fetch records wrt latest date

yoganantha321 — Mon, 27 Mar 2017 10:10:23 GMT

May be the below link is useful:

Get Max Date and Max Value of a ID