topic Re: Data extract in script based on max dates? in QlikView

Data extract in script based on max dates?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I have a question, what would be the best way to extract data for only the max 4 dates from the script itself. For example in the below data set I would like to get the data only for 5/3/2018, 2/15/2018,3/1/2018 and 12/23/2018 (as these are the top 4 max dates in the data set)

DDate, number

2/15/2018, 1

3/1/2018, 2

4/26/2017, 3

5/3/2018, 4

12/23/2018, 5

11/11/2011, 6

5/3/2018, 7

Thanks for the help

Re: Data extract in script based on max dates?

sunny_talwar — Tue, 20 Feb 2018 16:45:20 GMT

May be like this

Table:

LOAD DDate,

number

FROM ...;

Left Join (Table)

LOAD Max(DDate, 4) as Max4Date

Resident Table;

FinalTable:

LOAD DDate,

number

Resident Table

Where DDate >= Max4Date;

DROP Table Table;

Re: Data extract in script based on max dates?

alis2063 — Tue, 20 Feb 2018 18:24:22 GMT

Hi Sunny,

it does not workout,

Table:

load * inline

[Date,Number

01-Jan-2014,1

07-Jan-2014,2

08-Jan-2014,3

06-Jan-2014,4

10-Jan-2014,5

];

left join(Table)

load Max(Date,4) as Max4Date

Resident Table;

FinalTable:

LOAD Date,

Number

Resident Table

Where Date>= Max4Date;

DROP Table Table;

is there something wrong in script??

Re: Data extract in script based on max dates?

alis2063 — Tue, 20 Feb 2018 18:27:53 GMT

or either is cause of data type issues?

Re: Data extract in script based on max dates?

sunny_talwar — Tue, 20 Feb 2018 18:28:33 GMT

Seems to be working fine for me... did your Date read as Date field by QlikView?

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MMM-YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Table:

LOAD * INLINE [

Date, Number

01-Jan-2014, 1

07-Jan-2014, 2

08-Jan-2014, 3

06-Jan-2014, 4

10-Jan-2014, 5

];

Left Join(Table)

LOAD Max(Date,4) as Max4Date

Resident Table;

FinalTable:

LOAD Date,

Number

Resident Table

Where Date >= Max4Date;

DROP Table Table;

Re: Data extract in script based on max dates?

alis2063 — Tue, 20 Feb 2018 18:31:01 GMT

Thanks buddy for quick response.

Its working now.....

Re: Data extract in script based on max dates?

Anonymous — Tue, 20 Feb 2018 18:48:08 GMT

Thanks Sunny. It is working for my use case.