Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
brunopaulo
Partner
Partner

MaxDate in script

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

4 Replies
chrismarlow
Specialist II
Specialist II

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.

adityaakshaya
Creator III
Creator III

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

manoranjan_d
Specialist
Specialist

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;

CarlosAMonroy
Creator III
Creator III

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