More than 100 If statement alternative

[Abhineo]

Hi, I coded below statement however got error expression in 100th nested if, however i need to continue code it for 365 days(number of year days. Please help, if i can use any alternative to if.

=if (Date = Model_Start_Date, $(Day1ML),
IF(Date=Date(Model_Start_Date+1),($(Day2ML)),
:
Model_Start_Date))))))))))) ))))))))))) ))))))))))) ))))))))))) ))))))))))) ))))))))))) )))))))))) ))))))))))) )))))))))) ))

[marcus_sommer]

The use of pick(match()) would be an alternatively to the nested if-loops but I think there are other and better ways which creates your needed results within a more generic approach as applying such huge constructs with hundreds of branches within the expressions and also using so many variables.

What is the aim behind it and what contain the variables?

- Marcus  

[Kushal_Chawda]

@Abhineo  Not sure what your variable contains . Looks like you are comparing dates with incrementing one of the date which is not ideal way of doing it. Probably you can join both dates to get the desire value. It will look something like below

Data:
LOAD 
Date,
Model_Start_Date,
Value
FROM table;

left join
load Model_Start_Date as Date,
     Value as Value2
     1 as Matched_Flag
resident Data;

Now you can simply refer to Value2 with Date considering the Matched_Flag

I am not sure about this as I don't know the actual scenario. Just guessing based on your if conditions. Probably we need more info about your scenarios with sample data.

[Abhineo]

Thanks, I should give brief of coding and variables used. Model_Start_date is constant, however Date Field is 365 days date, and variables Day*ML are having percentage increment calculation for some measures and finally each day percentage calculated values get projected in form of line chart for a year. 

i cam this far but now stuck on 100 line error.

[Kushal_Chawda]

@Abhineo  Variable calculation Day*ML.. So what is day and what is ML?

[Abhineo]

Day and ML is just words, here i meant * for numbers between 1 to 365. Each Variable has separate calculation, fixed percentage for each day. 

[Abhineo]

Thanks, I tried pick, but some how it only matching for Model_Start_Date, not the others.

Pick(Match(Date,Model_Start_Date, Date($(Model_Start_Date)+1)..........),$(Day1ML),$(Day2ML).......)

It only taking variable Day1ML value correctly, not others.

Here ..... means continuous coding for all 365 days Date and Day variables. 

[Kushal_Chawda · Accepted solution]

@Abhineo  try to change your pick match as below

Pick(Match(-1,

Date=Model_Start_Date, Date=Model_Start_Date+1......),$(Day1ML),$(Day2ML).,......)

or I would suggest to share some sample data with expected output to help you out with better way of doing it

[Abhineo]

Solution is working, thanks for it @Kushal_Chawda but got big performance issue, since using many variables in calculation. I think it need redesigned. Day1ML like variables are for 365 days in year. For each day certain percent is fixed to calculate expected load. Input load quantity is coming out from previous calculation. Previous calculation of load quantity is again in variables, having if condition to check if input load quantity for the date, is not increasing maximum allowable limit(suppose 50K) and if increasing then more load than 50K should get add to next day, same check there total not increasing more than 50K like wise. .. 

Chart is cumulative of final Day1ML ,...... variables... 

Please help if you can.

[Kushal_Chawda]

@Abhineo  what is the expression in Day1ML variable?