Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
supritha_collie
New Contributor III

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

Re: Max of date

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
Contributor II

Re: Max of date

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
Esteemed Contributor III

Re: Max of date

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]);

Re: Max of date

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;

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)

Re: Max of date

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;

Re: Max of date

What is the error message you get when it fails?