3 Replies Latest reply: Mar 30, 2016 9:32 AM by Sunny Talwar RSS

    Fetch last second date from table

    Qlik View

      Hi All,

       

      I have a table in which i have some fields. I have a date column which has multiple dates . just need to fetch last 2nd date for respective dimension. Using set analysis we can do it in front end..i need this in script. attached is the sample QVW and sample screen shot.

       

      For Australia 2/3/2016 is the max date which is showing. i need 5/3/2016 which is 2nd largest date.

        • Re: Fetch last second date from table
          Sunny Talwar

          May be this:

          Tablee:

          LOAD * INLINE [

              Country, State, City, date, SaleValue

              India, Maharashtra, Mumbai, 1/1/2016, 100

              India, Maharashtra, Mumbai, 2/1/2016, 200

              India, Maharashtra, Mumbai, 5/1/2016, 900

              India, Maharashtra, Mumbai, 7/1/2016, 1500

              India, Maharashtra, Mumbai, 8/1/2016, 500

              India, Maharashtra, Pune, 1/2/2016, 600

              India, Maharashtra, Pune, 2/2/2016, 400

              India, Maharashtra, Pune, 5/2/2016, 900

              India, Maharashtra, Pune, 6/2/2016, 500

              India, Maharashtra, Pune, 9/2/2016, 600

              India, Gujrat, Badoda, 1/4/2016, 300

              India, Gujrat, Badoda, 2/4/2016, 300

              India, Gujrat, Badoda, 5/4/2016, 2000

              India, Gujrat, Badoda, 8/4/2016, 9000

              India, Gujrat, Badoda, 9/4/2016, 500

              Sydney ,New South Wales,Australia,2/3/2016,900

              Sydney ,New South Wales,Australia,5/3/2016,700

              Sydney ,New South Wales,Australia,6/3/2016,200

              Sydney ,New South Wales,Australia,7/3/2016,7700

              Sydney ,New South Wales,Australia,8/3/2016,800

              US ,ABC,Washington,1/5/2016,1000

              US ,ABC,Washington,5/5/2016,3000

              US ,ABC,Washington,7/5/2016,9000

              US ,ABC,Washington,8/5/2016,8000

              US ,ABC,Washington,9/5/2016,7000

              US ,XYZ,Salem,2/9/2016,1000

              US ,XYZ,Salem,4/9/2016,10000

              US ,XYZ,Salem,5/9/2016,13000

              US ,XYZ,Salem,7/9/2016,14000

              US ,XYZ,Salem,9/9/2016,11000

          ];

           

          Left Join (Tablee)

          LOAD Country,

            Date(Min(date)) as MinDate,

            Date(Min(date, 2)) as MinDate2,

            FirstSortedValue(SaleValue, date, 2) as MinDate2Sales

          Resident Tablee

          Group By Country;

          Capture.PNG

            • Re: Fetch last second date from table
              Qlik View

              Here the dates are reflecting wrong. may be due to date format. In Badoda city there is no min date 1/1/2016....

                • Re: Fetch last second date from table
                  Sunny Talwar

                  I did it at the Country level. Do you want the minDate at City Level?

                   

                  UPDATED Script:

                  Tablee:

                  LOAD * INLINE [

                      Country, State, City, date, SaleValue

                      India, Maharashtra, Mumbai, 1/1/2016, 100

                      India, Maharashtra, Mumbai, 2/1/2016, 200

                      India, Maharashtra, Mumbai, 5/1/2016, 900

                      India, Maharashtra, Mumbai, 7/1/2016, 1500

                      India, Maharashtra, Mumbai, 8/1/2016, 500

                      India, Maharashtra, Pune, 1/2/2016, 600

                      India, Maharashtra, Pune, 2/2/2016, 400

                      India, Maharashtra, Pune, 5/2/2016, 900

                      India, Maharashtra, Pune, 6/2/2016, 500

                      India, Maharashtra, Pune, 9/2/2016, 600

                      India, Gujrat, Badoda, 1/4/2016, 300

                      India, Gujrat, Badoda, 2/4/2016, 300

                      India, Gujrat, Badoda, 5/4/2016, 2000

                      India, Gujrat, Badoda, 8/4/2016, 9000

                      India, Gujrat, Badoda, 9/4/2016, 500

                      Sydney ,New South Wales,Australia,2/3/2016,900

                      Sydney ,New South Wales,Australia,5/3/2016,700

                      Sydney ,New South Wales,Australia,6/3/2016,200

                      Sydney ,New South Wales,Australia,7/3/2016,7700

                      Sydney ,New South Wales,Australia,8/3/2016,800

                      US ,ABC,Washington,1/5/2016,1000

                      US ,ABC,Washington,5/5/2016,3000

                      US ,ABC,Washington,7/5/2016,9000

                      US ,ABC,Washington,8/5/2016,8000

                      US ,ABC,Washington,9/5/2016,7000

                      US ,XYZ,Salem,2/9/2016,1000

                      US ,XYZ,Salem,4/9/2016,10000

                      US ,XYZ,Salem,5/9/2016,13000

                      US ,XYZ,Salem,7/9/2016,14000

                      US ,XYZ,Salem,9/9/2016,11000

                  ];

                   

                  Left Join (Tablee)

                  LOAD Country,

                    State,

                    City,

                    Date(Min(date)) as MinDate,

                    Date(Min(date, 2)) as MinDate2,

                    FirstSortedValue(SaleValue, date, 2) as MinDate2Sales

                  Resident Tablee

                  Group By Country, State, City;

                   

                  Capture.PNG