12 Replies Latest reply: Jun 6, 2016 5:56 AM by James Bond RSS

    Display records for latest date

    James Bond

      I have the following data in my QVD

       

      ProductFromTo
      DEE00105952015-02-032015-03-03
      DEE00105952015-03-042015-12-31
      DEE00105952015-04-179999-12-31
      DEE00105952016-01-019999-12-31
      DEE00124742015-03-042015-12-31
      DEE00124742016-01-019999-12-31
      DEE00128812015-03-032015-03-03
      DEE00128812015-03-042015-12-31
      DEE00128812016-01-019999-12-31
      DEE00131962015-12-302015-12-31
      DEE00131962016-01-019999-12-31
      DEE00131962016-01-019999-12-31
      DEE00138042015-03-042015-12-31
      DEE00138042016-01-019999-12-31
      DEE00313752015-12-302015-12-31
      DEE00313752016-01-019999-12-31
      DEE00313752016-01-019999-12-31
      DEE00387292015-03-042015-12-31
      DEE00387292016-01-019999-12-31

       

       

      I have loaded all three columns in my report and displaying them as dimension. My requirement is to display the products (without duplicates) with latest 'from' date.

       

      For Example:

      P                    From          To

      DEE00105952016-01-019999-12-31
      DEE00124742016-01-019999-12-31

      and so on....

       

      Can this be done without changing the following load script ?

       

      TABLE:

      LOAD Product AS P,

           date(max(From),'YYYY-MM-DD') AS From,

           To AS To

      FROM [table.qvd](qvd)

      WHERE Interval(date(Today(),'MM/DD/YYYY') - (date(AddMonths(Date(Today()), -6),'MM/DD/YYYY')),'d') >= 180

      GROUP BY Product, To;

       

       

       

       

      TIA,

      James

        • Re: Display records for latest date
          Sunny Talwar

          May be like this:

           

          TABLE:

          LOAD Product AS P,

               Date(Max(Date#(From, 'YYYY-MM-DD')), 'YYYY-MM-DD') as From,

               FirstSortedValue(Date#(To, 'YYYY-MM-DD'), Date#(From, 'YYYY-MM-DD')) as To

          FROM [table.qvd](qvd)

          Group By Product;

           

          Not sure what the intent of the where statement is?

            • Re: Display records for latest date
              Sunny Talwar

              This what I got for your sample data:

               

              Capture.PNG

                • Re: Display records for latest date
                  James Bond

                  Thanks for your support Sunny , but the 'To' column is sorted separately, which changed the original 'To'values (which is wrong). As i said before for every product latest 'From' date shoud be identified and the entire row should be displayed. (See samples below)

                   

                  Product                 From          To

                  DEE00105952016-01-019999-12-31
                  DEE00124742016-01-019999-12-31

                  For the product DEE0010505 latest 'From' date out of 3 entries is '2016-01-01' and the corresponding 'To' date is '9999-12-31' . But in your result it was changed as '2015-03-03' which is not correct. Can you please look into it ?

                   

                  TIA,

                  James

                    • Re: Display records for latest date
                      Sunny Talwar

                      My bad, here is the new code:

                       

                      Table:

                      LOAD Product as P,

                          Date(Max(Date#(From, 'YYYY-MM-DD')), 'YYYY-MM-DD') as From,

                          FirstSortedValue(DISTINCT Date#(To, 'YYYY-MM-DD'), -Date#(From, 'YYYY-MM-DD')) as To

                      FROM

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

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

                      Group By Product;


                      Capture.PNG

                        • Re: Display records for latest date
                          James Bond

                          distinct error.PNG

                          My QlikView is not detecting 'DISTINCT ' keyword in blue color inside 'FirstSortedValue' context . Please help.

                          FYI: I even copied your new code and tried. But no luck

                           

                          Thanks,

                          James

                            • Re: Display records for latest date
                              Sunny Talwar

                              Even though it doesn't detect the DISTINCT keyword, have you tried reloading the application? There have been times when QlikView expression editor won't recognize something as correct syntax, but it would run through (and distinct with FirstSortedValue did use to have that problem). Give it a shot by running it and see what you get

                                • Re: Display records for latest date
                                  James Bond

                                  Yaay ! It works !! Thanks a lot ! You are a GEM !!!  

                                  • Re: Display records for latest date
                                    James Bond

                                    Hello stalwar1,

                                    There is a small change to be done as follows.

                                         - Latest 'From' date per product should be displayed which should be 180 days older from today.

                                         - I think the line 'WHERE Interval(date(Today(),'MM/DD/YYYY') - (date(AddMonths(Date(Today()), -6),'MM/DD/YYYY')),'d') >= 180' which i added in the original post should work. Isn't it ?

                                     

                                    TIA,

                                    James

                                      • Re: Display records for latest date
                                        Sunny Talwar

                                        Would you be able to elaborate what the expected output will look like in the sample above?

                                          • Re: Display records for latest date
                                            James Bond

                                            Sure,

                                             

                                            Data :

                                            Product  From          To

                                              A            2014-01-01    9999-12-31

                                              A            2015-01-11    9999-12-31

                                              B            2014-05-05    9999-12-31

                                              B            2016-05-12    9999-12-31

                                              B            2015-05-21    9999-12-31

                                              C            2014-02-09    9999-12-31

                                              C            2015-02-19    9999-12-31

                                              C            2013-03-27    9999-12-31

                                              D            2016-04-16    9999-12-31

                                              D            2012-04-06    9999-12-31

                                             

                                            The expected output is,

                                             

                                            ProductFromTo

                                            A            2015-01-01    9999-12-31  

                                            C            2015-02-19    9999-12-31


                                            Products B and D should not be displayed as their Maximum of 'From' are not older than 180 days from today's date.

                                            For B:

                                            Num( 2016-06-03 - 2016-05-12) = 23

                                            For D:

                                            Num(2016-06-03 - 2016-04-16) = 49

                                            Hope you got it.
                                            Thanks,
                                            James
                                              • Re: Display records for latest date
                                                Sunny Talwar

                                                Try like this:

                                                 

                                                Table:

                                                LOAD *

                                                Where From < Today() - 180;

                                                LOAD Product as P,

                                                    Date(Max(Date#(From, 'YYYY-MM-DD')), 'YYYY-MM-DD') as From,

                                                    FirstSortedValue(DISTINCT Date#(To, 'YYYY-MM-DD'), -Date#(From, 'YYYY-MM-DD')) as To

                                                Group By Product;

                                                LOAD * INLINE [

                                                    Product,  From,          To

                                                      A,      2014-01-01,    9999-12-31

                                                      A,      2015-01-11,    9999-12-31

                                                      B,      2014-05-05,    9999-12-31

                                                      B,      2016-05-12,    9999-12-31

                                                      B,      2015-05-21,    9999-12-31

                                                      C,      2014-02-09,    9999-12-31

                                                      C,      2015-02-19,    9999-12-31

                                                      C,      2013-03-27,    9999-12-31

                                                      D,      2016-04-16,    9999-12-31

                                                      D,      2012-04-06,    9999-12-31

                                                ];

                                                 

                                                Capture.PNG