topic Re: Fetch last second date from table in QlikView

Fetch last second date from table

help4qv123 — Wed, 30 Mar 2016 13:15:08 GMT

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 — Wed, 30 Mar 2016 13:19:04 GMT

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;

Re: Fetch last second date from table

help4qv123 — Wed, 30 Mar 2016 13:28:50 GMT

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 — Wed, 30 Mar 2016 13:30:43 GMT

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;