topic Re: max date in QlikView

max date

Anonymous — Wed, 27 Nov 2013 23:32:11 GMT

HI,

I've a table like this:

Date Amount OTHER FIELDS

2013-09-12 555

2013-09-18 666

2013-10-12 11111 ccxx

2013-10-28 12131 xxx

2013-11-07 1111

2013-11-15 33333

2013-11-27 4444

I would have a table with the max date for each month, like this

2013-09-18 -.....

2013-10-28 .......

2013-11-27

How can I solve it ?

Help please

Thanks

Re: max date

Carlos_Reyes — Thu, 28 Nov 2013 00:04:00 GMT

I would do it in the script:

Table1:

LOAD

Date,

Amount,

Field1,

Field2

FieldN,

MonthName(Date) AS MonthYear,

FROM YOURSOURCE;

Table2:

LOAD

MonthYear,

MAX(Date) AS LastDateofMonth

RESIDENT Table1

GROUP BY MonthYear;

Re: max date

jagan — Thu, 28 Nov 2013 03:20:26 GMT

Table1:

LOAD

Date,

Amount,

FROM Source1;

INNER JOIN (Table1)

LOAD

MonthYear,

Date(MAX(Date), 'YYYY-MM-DD') AS Date

RESIDENT Table1

GROUP BY MonthYear;

Now your table has only Max date rows of every month.

Regards,

Jagan.

Re: max date

Anonymous — Mon, 02 Dec 2013 21:08:48 GMT

Thanks to everybody !!

Re: max date

Fri, 20 Dec 2013 03:34:56 GMT

hello even i have same problem can you please help me out

cid cdate ctime phone

101 10-dec-2013 10.15 1

101 11-dec-2013 5.15 2

102 10-dec-2013 3.15 3

102 10-dec-2013 4.15 4

103 10-dec-2013 6.15 5

103 10-dec-2013 7.15 6

103 11-dec-2013 8.15 7

i need for max(date ) in that max(date) i need max( time ) for coustmer 101,102 ,103 in script level please help me out

Re: max date

Fri, 20 Dec 2013 04:30:42 GMT

Hi Bibopipo,

Try this code ,

Dev01:

Load * Inline

[

Date,Amount,OTHER_FIELDS

2013-09-12,555

2013-09-18,666

2013-10-12,11111

2013-10-28,12131

2013-11-07,1111

2013-11-15,33333

2013-11-27,4444

];

LOAD max(Date(Date,'MM-DD-YYYY')) as Max_Date Resident Dev01

Group by Date;

Re: max date

Fri, 20 Dec 2013 04:57:03 GMT

For Max of Date & Max of time seperately, you can use FirstValue Function. Order the Data set on Date & Time (Both decending) & use FirstValue(Date) & FirstValue(Time).

This should do the trick.