Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
help4qv123
Contributor

Fetch last second date from table

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.

3 Replies

Re: Fetch last second date from table

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

help4qv123
Contributor

Re: Fetch last second date from table

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

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


Community Browser