topic Re: Max function not working in QlikView

Max function not working

Thu, 14 Jul 2016 10:00:29 GMT

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

Re: Max function not working

Anonymous — Thu, 14 Jul 2016 12:08:16 GMT

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

Re: Max function not working

Gysbert_Wassenaar — Thu, 14 Jul 2016 12:08:48 GMT

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.

Re: Max function not working

sunny_talwar — Thu, 14 Jul 2016 12:09:17 GMT

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';

SET DateFormat='D/M/YYYY';

Re: Max function not working

qlikview979 — Thu, 14 Jul 2016 12:10:28 GMT

Hi ,

Can you share your source file.

Re: Max function not working

Thu, 14 Jul 2016 12:57:12 GMT

Hi Sunny,

That was correct! Thanks a lot!

Re: Max function not working

Mark_Little — Thu, 14 Jul 2016 13:06:27 GMT

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

Re: Max function not working

squeakie_pig — Thu, 14 Jul 2016 13:26:25 GMT

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