Data extract in script based on max dates?

[Anonymous]

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

[sunny_talwar · Accepted solution]

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;

[alis2063]

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??

[alis2063]

or either is cause of data type issues?

[sunny_talwar]

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;

[alis2063]

Thanks buddy for quick response.

Its working now.....

[Anonymous]

Thanks Sunny. It is working for my use case.