Let statement in load script Variable

[#1]

Hello Im trying to create a max(Year) variable in load script instead of creating this in variable overview,

The variable gets created , but when i choose a different Year, it stays solid on the Max Year, where should i put the dollar expansion so the variable gets more dynamic ?

DATA:

LOAD ID,

     FirstName,

     LastName,

     Email,

     Country,

     Date,

     Year(Date) as Year,

     Month(Date) as Month

FROM

(ooxml, embedded labels, table is data);

Temp:

load max(Year) as MaxYear

resident  DATA;

let vMaxYear =  num(floor(Peek('MaxYear')));

drop table Temp;

[settu_periasamy · Accepted solution]

Because of the Temp Table Aggregation with Max(Year).

You will get only one Value in the Temp Table . i.e Maximum Year.

If you want to use the Maximum Year(vMaxYear) in the Script you can use

LET vMaxYear=num(floor(Peek('MaxYear')));

if you want the Max year as dynamic, you can create the variable Using SET statement. Like

SET vMaxYear='=Max(YearField)';

Script

DATA:

LOAD ID,

     FirstName,

     LastName,

     Email,

     Country,

     Date,

     Year(Date) as Year,

     Month(Date) as Month   

FROM

(ooxml, embedded labels, table is data);

SET vMaxYear='=Max(Year)';

if you want you can use the below script..

Temp:

load max(Year) as Year1

resident  DATA;

let vMaxYear1 =  num(floor(Peek('Year1')));

drop table Temp;

[#3]

Hello, worked perfectly!