Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
BI_Dev
Creator II
Creator II

Max of date

KeyDate:

LOAD

  

    Id,

   max([KeyDate]) as KeyDate

   

FROM (XmlSimple, Table is [Data/KeyDate])

group by Id;

I have this script to just pull max date fro each ID and when I relaod it just fails.Not sure what I am doing wrong here.Any help is appreciated.Thank you.

6 Replies
its_anandrjs

Try first load the table and then load with group by see script

tmpKeyDate:

LOAD

  Id,

  [KeyDate]

FROM (XmlSimple, Table is [Data/KeyDate]);

Noconcatenate

KeyMaxDate:

Load

  Id,

   max([KeyDate]) as KeyDate

Resident tmpKeyDate

group by Id;


Drop Table tmpKeyDate;

suryaa30
Creator II
Creator II

Sol1://///// RESIDENT LOAD

TempKeyDate:

LOAD

     Id,

KeyDate

 

FROM (XmlSimple, Table is [Data/KeyDate]);

KeyDate:

NOCONCATENATE

LOAD

    Id,

   max([KeyDate]) as KeyDate

   RESIDENT TempKeyDate

group by Id;

----------------------------------------------------------------------------------------------

Sol 2://///// Preceding LOAD

KeyDate:

LOAD

    Id,

   max([KeyDate]) as KeyDate

  group by Id;

LOAD

     Id,

KeyDate

   FROM (XmlSimple, Table is [Data/KeyDate]);

vishsaggi
Champion III
Champion III

What is the error you are getting?

Try like below?

KeyDate:

LOAD

  Id,

  max([KeyDate]) as Key_Date

FROM (XmlSimple, Table is [Data/KeyDate])

group by Id;

OR

KeyDate:

LOAD id, Max(KeyDate) AS Key_Date

Group By id;

LOAD

  Id,

  [KeyDate]

FROM (XmlSimple, Table is [Data/KeyDate]);

Anil_Babu_Samineni

Is this script reading the data?

KeyDate:

LOAD

    Id,

   KeyDate  

FROM (XmlSimple, Table is [Data/KeyDate]);

If so, Try this?

KeyDate:

LOAD

    Id,

   KeyDate  

FROM (XmlSimple, Table is [Data/KeyDate]);

Final:

NoConcatenate

Load Id, Max(KeyDate) as KeyDate

Resident KeyDate

Group By Id;

Drop Table KeyDate;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
its_anandrjs

Another way to solve it better way is create the QVDs and use this

tmpKeyDate:

LOAD

  Id,

  [KeyDate]

FROM (XmlSimple, Table is [Data/KeyDate]);

Store tmpKeyDate into tmpKeyDate.qvd(qvd);

Drop Table tmpKeyDate;

KeyMaxDate:

Load

  Id,

   max([KeyDate]) as KeyDate

From tmpKeyDate.qvd(qvd)

group by Id;

sunny_talwar

What is the error message you get when it fails?