topic Re: Creating a Variable for the Max Date in QlikView

Creating a Variable for the Max Date

matthewjbryant — Wed, 01 Jul 2015 10:29:40 GMT

I have a script that creates a table of all dates from file names, and I am trying to create a variable from this list to pick out the max date:

For Each vFile in FileList('\D_OO_*.qvd')

FileDates:
Load

Date(Date#(Left(Right('$(vFile)',12),8),'YYYYMMDD'),'DD/MM/YYYY') as [DOO File Date]

autogenerate(1);

Next vFile;

LET vStatisticDate = max([DOO File Date]);

My problem is that for some reason the variable vStatisticDate is null, but when I use the exact same expression after the Load I have no problems. What am I doing wrong?

Re: Creating a Variable for the Max Date

alexandros17 — Wed, 01 Jul 2015 10:32:50 GMT

For Each vFile in FileList('\D_OO_*.qvd')

FileDates:
Load

Date(Date#(Left(Right('$(vFile)',12),8),'YYYYMMDD'),'DD/MM/YYYY') as [DOO File Date]

autogenerate(1);

Next vFile;

TMP:
LOAD max([DOO File Date]) as Maxi Resident FileDates;
LET vStatisticDate = Peek('Maxi',0,'TMP');
DROP Table TMP;

Re: Creating a Variable for the Max Date

MayilVahanan — Wed, 01 Jul 2015 10:33:04 GMT

Try like this

MaxDOOFileDate:

Load max([DOO File Date]) as MaxDate

Resident FileDates;

LET vStatisticDate = Peek('MaxDate', 0, 'MaxDOOFileDate');

Drop table MaxDOOFileDate;

Re: Creating a Variable for the Max Date

sunny_talwar — Wed, 01 Jul 2015 10:36:14 GMT

You need to use Peek in order for this to make it work:

Temp:

LOAD max([DOO File Date]) as MaxDate

Resident FilesDates;

LET vStatisticDate = Peek('MaxDate');

DROP Table Temp;

HTH

Best,

Sunny

Re: Creating a Variable for the Max Date

matthewjbryant — Wed, 01 Jul 2015 10:45:03 GMT

Awesome. Thanks. So is the table not compiled correctly by that point?