topic MaxDate in script in QlikView

MaxDate in script

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

Hi Community,

I have this problem

Field1 | Field 2 | Field3 | Date

1 1 1 28-08-2018

1 1 1 29-08-2018

1 2 2 30-08-2018

1 2 2 09-06-2016

2 1 1 09-06-2016

Expect result

Field1 | Field 2 | Field3 | Date

1 1 1 29-08-2018

1 2 2 30-08-2018

2 1 1 09-06-2016

Im trying do this

Table:

Load

Field1,

Field2,

Field3,

max(date) as date

Resident TableTEMP

Group by

Field1,

Field2,

Field3

;

Drop table TableTEMP;

But because the script is line by line i belive thats the reason dat isnt working. Any ideas?

Best Regards

Bruno Paulo

Re: MaxDate in script

chrismarlow — Tue, 28 Aug 2018 16:44:24 GMT

I had to do a couple of things to come up with your answer;

1) Change the date setting at the top of the script - line 7 in mine

SET DateFormat='DD-MM-YYYY';

2) I had to put a 'NoConcatenate' on the load, or it kept bolting the grouped table back on to the temp (& then deleting it);

Table:

noconcatenate

Load

Field1,

Does either/both of those help?

Regards,

Chris.

Re: MaxDate in script

adityaakshaya — Tue, 28 Aug 2018 16:47:24 GMT

Hi Bruno,

I don't see any problem with your code.

Your code will work fine if you have data in Date field is in date format.

You can change the data in date field as date.

Regards,

Akshaya

Re: MaxDate in script

manoranjan_d — Wed, 26 Sep 2018 16:09:55 GMT

try this

TableTEMP:

LOAD * INLINE [

Field1, Field2, Field3, Date

1, 1, 1, 28-08-2018

1, 1, 1, 29-08-2018

1, 2, 2, 30-08-2018

1, 2, 2, 09-06-2016

2, 1, 1, 09-06-2016

];

NoConcatenate

Table:

Load

Field1,

Field2,

Field3,

date(max(date(date#(Date,'DD-MM-YYYY'))),'DD-MM-YYYY') as Date

Resident TableTEMP

Group by

Field1,

Field2,

Field3

;

Drop table TableTEMP;

Re: MaxDate in script

CarlosAMonroy — Wed, 26 Sep 2018 17:08:20 GMT

Hi Bruno,

It should work the way you're doing it. Getting the expected result:

TableTmp:

LOAD * INLINE [

F1, F2, F3, Date

1, 1, 1, 28-08-2018

1, 1, 1, 29-08-2018

1, 2, 2, 30-08-2018

1, 2, 2, 09-06-2018

2, 1, 1, 09-06-2018

];

NoConcatenate

Table:

LOAD Distinct F1, F2, F3, Date(Max(Date#(Date,'DD-MM-YYYY'))) as Date Resident TableTmp

Group By F1, F2, F3

;

DROP Table TableTmp;

Hope that helps,

Carlos M