Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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?