Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
See attached file
hope this helps
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.