Creating a Variable for the Max Date

[matthewjbryant]

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?

[alexandros17 · Accepted solution]

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;

[MayilVahanan]

Hi

Try like this

MaxDOOFileDate:

Load max([DOO File Date]) as MaxDate

Resident FileDates;

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

Drop table MaxDOOFileDate;

[sunny_talwar]

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

[matthewjbryant]

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