Max function not working

[#1]

Hi people,

I trying to fetch the max date from a qvd.

My script is something like this:

Vendor:

LOAD Vendor,

     Product,

     Mod_Date,

     Price

FROM

C:\Users\madhura.nadgauda\Documents\Incremental_Load_Data.xlsx

(ooxml, embedded labels, table is Sheet1);

latest_date:

load

  max(Mod_Date) as max_date

Resident Vendor;

With max_date I am trying to fetch the latest date from vendor table, but irrespective of whatever function I use, I always get the first date in the table i.e. 12/7/2016 instead of 17/7/2016 (max). Please help

[Anonymous]

Hi

I think you have to sort the field "Mod_date" first and do the calucations ...

[Gysbert_Wassenaar]

The load statement you posted loads data from an excel file, not a qvd. If max(Mod_Date) doesn't return the value you expect then that value does not exist in your data. At least not as a numeric value. Perhaps it's a text value.

[sunny_talwar · Accepted solution]

Can you check your environmental variable... I think your date is interpreted as MM/DD/YYYY instead of DD/MM/YYYY.

Make sure you have this in your environmental variables:

SET DateFormat='DD/MM/YYYY';

or

SET DateFormat='D/M/YYYY';

[qlikview979]

Hi ,

Can you share your source file.

[#6]

Hi Sunny,

That was correct! Thanks a lot!

[Mark_Little]

Hi,

I agree with Sunny and GD, 

Your problem will most likely down to the way your date is being interpreted. I would follow Sunny's instructions and also wrap you Mod_Date in a date function to i.e. DATE(Mod_Date) as Mod_Date.

Mark

[squeakie_pig]

Your code works if it is a date field, so you might want to force the value from the excel file to be a date field.

LOAD Vendor,

     Product,

     date(Mod_Date),

     Price

FROM

C:\Users\madhura.nadgauda\Documents\Incremental_Load_Data.xlsx

(ooxml, embedded labels, table is Sheet1);