Set Expression in Load Script

[Anonymous]

How can i achieve this below expression in load script?

Sum({$<YearMonthNumber = {"$(=Date(Date#(Max(YearMonthNumber),'YYYYMM')-1,'YYYYMM'))"}, Month = , MonthYearName = , Year = >} [Opening Stock])

Regards

[shraddha_g]

MaxDate:

Load Max(YearMonthNumber) as YearMonthNumber

From XYZ;

Let vYearNumber = peek('YearMonthNumber',0,'MaxDate');

Drop table MaxDate;

FinalTable:

Load Field1,

Field2,

YearMonthNumber,

Sum("Opening Stock") as "Opening Stock"

From XYZ

Where YearMonthNumber = $(vYearNumber)

Group by Field1,Field2,YearMonthNumber;

[VishalWaghole]

Small Correction in above logic,

MaxDate:

Load Max(YearMonthNumber)-1 as YearMonthNumber

From XYZ;

Let vYearNumber = peek('YearMonthNumber',0,'MaxDate');

Drop table MaxDate;

FinalTable:

Load Field1,

Field2,

YearMonthNumber,

Sum("Opening Stock") as "Opening Stock"

From XYZ

Where YearMonthNumber = $(vYearNumber)

Group by Field1,Field2,YearMonthNumber;

Regards,

Visahl Waghole

[sunny_talwar]

I suggest looking into The As-Of Table