Fetch last second date from table

[help4qv123]

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.

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

[help4qv123]

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

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