Conditional Load

[joseph_bajo]

I am trying to do a conditional load based upon a date in the data. I am trying to use a preceding load statement, but get the error "field not found". I think there is a problem with the variable I set:

Load ...

     Date(OrderDate) as OrderDate

...;

let vRefDate = Year(Max(OrderDate));

Load ...

WHERE Year(OrderDate) < vRefDate;

[sunny_talwar · Accepted solution]

You cannot use Aggregation directly in your variable... try like this

Fact:

Load ...

    Date(OrderDate) as OrderDate

...;

Max:

LOAD Year(Max(OrderDate)) as MaxOrderDate

Resident Fact;

LET vRefDate = Peek('MaxOrderDate');

LOAD

WHERE Year(OrderDate) < vRefDate;

[joseph_bajo]

Thanks- this only returns 1 row though, and I want all data before this certain year. I think this is because there is only one row in the "Max" table.

[sunny_talwar]

What exactly do you have and what do you need to get?

[joseph_bajo]

Thanks Sunny, I have figured it out.