Define a field with variable comparison

[Brice1]

Hi all,

I want to define a field with an expression in wich I compare another field with 2 variables.

There is a part of my script:

set vCurrent = max(if(Extract_date < today() and Flag_freeze = '1',Extract_date));
set vNext = min(if(Extract_date > today() and Flag_freeze = '1',Extract_date));

Extract_temp:
Load
   *,
   if(Extract_date >= $(vCurrent),
      if(Extract_date < $(vNext),'Current')) as Current_freeze
Resident Extract_table;

The bug report said I have an invalid expression for the Red text.

It's not possible to use variables to define a field?

Thanks for your support.

[Frank_Hartmann]

Not sure if i met your if condition in script correctly so maybe you will need to adapt.

and I guess that you need a group by in script because your Variable contains an aggregation:

try like that:

Extract_table:
Load * Inline [
Extract_date, Flag_freeze
23.04.2019, 1
31.06.2019, 1
23.02.2019, 1
07.07.2019, 1
20.09.2019, 1
31.07.2019, 1
23.02.2019, 1
30.08.2019, 1
];


Let vCurrent = 'max(if(Extract_date < today() and Flag_freeze =1 ,Extract_date))';
Let vNext = 'max(if(Extract_date > today() and Flag_freeze = 1,Extract_date))';


NoConcatenate


Extract_temp:
Load
   Flag_freeze,Extract_date,
   if(Extract_date >= date($(vCurrent)),date($(vCurrent)),
      if(Extract_date < date($(vNext)),date($(vNext)),'Current')) as Current_freeze
Resident Extract_table Group by Flag_freeze,Extract_date ; DROP Table Extract_table;

[Brice1]

Thanks Frank for your answer.

I have no problem on my expression now but it does not work fine...

I want a new column (Current_freeze) with the value 'Current' if the Extract_date is between the variable vCurrent and vNext.

it seems in the expression, operators >= & < are not taken into account.

[Frank_Hartmann · Accepted solution]

See attached file

hope this helps

[Brice1]

It works well!
Thanks Frank.
Strange idea to define variables as a field... I couldn't find it.

[Frank_Hartmann]

the probleme was, that your variables were calculated by row. but you will need to get a fix/static value for the variables in order to make your calculations, not a row by row calculated variable value. thats why i made a left join to find the coresponding variable values out of the whole column.