Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Fetch records wrt latest date

Dear community -

Here's the issue I'm facing -

   

  I/P Table :-  

DEPOT_CDPART_NOSTOCK_QTYUPD_DT_TM
S1000000189909/30/2015 0:02
S1000000189904/1/2016 0:12
S10000001899110/1/2013 0:02
S1000000189914/1/2014 0:11
S1000000189914/1/2015 0:11
S1000000189926/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_CDPART_NOSTOCK_QTYUPD_DT_TM
S1000000189904/1/2016 0:12

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

Thanks in advance

Sneh

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Fetch records wrt latest date



1.png


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

View solution in original post

5 Replies
Highlighted
Specialist II
Specialist II

Re: Fetch records wrt latest date

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

Highlighted
Creator
Creator

Re: Fetch records wrt latest date

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

Highlighted
MVP
MVP

Re: Fetch records wrt latest date



1.png


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

View solution in original post

Highlighted
Creator II
Creator II

Re: Fetch records wrt latest date

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

Highlighted
Creator II
Creator II

Re: Fetch records wrt latest date

May be the below link is useful:

Get Max Date and Max Value of a ID