Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have a dates list and employment ratio for each day loaded in script.
i need to find the next working day [next_active_day] when the [employment_ratio] is set 0.
i managed when the gap is about one day only , but when the gap is more then one day i'm lost (for exp the 5/4/23).
appreciate any help.
thanks.
@eladberko try below
Data:
Load * Inline [
Date, employment_ratio
01/04/2023,0
02/04/2023,1
03/04/2023,1
04/04/2023,1
05/04/2023,0
06/04/2023,0
07/04/2023,0
08/04/2023,0
09/04/2023,0.5
10/04/2023,0.5
11/04/2023,0.25
12/04/2023,0
13/04/2023,1 ];
New:
Load *,
if(RowNo()=1 or employment_ratio<>0,Date,
if(employment_ratio=0 and Previous(employment_ratio)<>0, Previous(Date),
if(employment_ratio=0 and Previous(employment_ratio)=0,Peek('next_active_day')))) as next_active_day
Resident Data
Order by Date desc;
Drop Table Data;
May be this
=Min({<Employment_Ratio={0}, Date={">$(=Max(Date))"}>} Date)
thank you, but i need the solution in script loader.
@eladberko try below
Data:
Load * Inline [
Date, employment_ratio
01/04/2023,0
02/04/2023,1
03/04/2023,1
04/04/2023,1
05/04/2023,0
06/04/2023,0
07/04/2023,0
08/04/2023,0
09/04/2023,0.5
10/04/2023,0.5
11/04/2023,0.25
12/04/2023,0
13/04/2023,1 ];
New:
Load *,
if(RowNo()=1 or employment_ratio<>0,Date,
if(employment_ratio=0 and Previous(employment_ratio)<>0, Previous(Date),
if(employment_ratio=0 and Previous(employment_ratio)=0,Peek('next_active_day')))) as next_active_day
Resident Data
Order by Date desc;
Drop Table Data;
perfect. thank you!