topic Re: More than 100 If statement alternative in QlikView

More than 100 If statement alternative

Abhineo — Thu, 15 Oct 2020 11:55:08 GMT

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))))))))))) ))))))))))) ))))))))))) ))))))))))) ))))))))))) ))))))))))) )))))))))) ))))))))))) )))))))))) ))

Re: More than 100 If statement alternative

marcus_sommer — Wed, 16 Sep 2020 14:09:58 GMT

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

Re: More than 100 If statement alternative

Kushal_Chawda — Wed, 16 Sep 2020 14:43:42 GMT

@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.

Re: More than 100 If statement alternative

Abhineo — Wed, 16 Sep 2020 16:18:10 GMT

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.

Re: More than 100 If statement alternative

Kushal_Chawda — Wed, 16 Sep 2020 16:52:03 GMT

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

Re: More than 100 If statement alternative

Abhineo — Thu, 17 Sep 2020 09:26:30 GMT

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.

Re: More than 100 If statement alternative

Abhineo — Thu, 17 Sep 2020 09:30:17 GMT

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.

Re: More than 100 If statement alternative

Kushal_Chawda — Thu, 17 Sep 2020 10:26:48 GMT

@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

Re: More than 100 If statement alternative

Abhineo — Thu, 01 Oct 2020 14:49:23 GMT

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.

Re: More than 100 If statement alternative

Kushal_Chawda — Thu, 01 Oct 2020 14:56:15 GMT

@Abhineo what is the expression in Day1ML variable?

Re: More than 100 If statement alternative

Abhineo — Thu, 01 Oct 2020 15:18:07 GMT

Hi, I have tried to reform below visual code in qlik by using variables for each day:

Visual basic code for percent calc is below:

Sub ModelPrep()

On Error GoTo ErrHandler

Dim r As Integer
Dim c As Integer
Dim campaignDay As Integer
Dim plannedCtr As Long ' running counter of daily planned
Dim plannedToday As Long
Dim plannedTarget As Long
Dim pending As Long

' clean-up... the range should ONLY include the burndown columns
Range("T6:NT371").Clear

MsgBox ("Building the model... this might take up to 2-3 minutes")

Application.ScreenUpdating = False

' campaignDay = 1
' totalPlanned = 0
' plannedToday = 0
' plannedCtr = 0
' pending = 0
'
' plannedTarget = Cells(1, 2).Value
'
' For r = 6 To 185 ' r = rows... 180 days in a typical pre-buy, row 6 is the first row of data
'
' plannedToday = Cells(1, 3).Value ' the planned number of downloads
'
' If (plannedToday + plannedCtr) > plannedTarget Then
' plannedToday = plannedTarget - plannedCtr
' End If
'
' plannedCtr = plannedCtr + plannedToday
'
' campaignDay = campaignDay + 1
'
' Next r

For r = 6 To 371 '185 ' r = rows... 180 days in a typical pre-buy
rr = r
For c = 20 To 385 '186
Cells(rr, c) = Cells(5, c).Value * Cells(r, 5) ' Cells(5,c) is the model!
rr = rr + 1
If rr > 371 Then Exit For
Next c
Next r

' ------------------------------------------------------------------
' Set cell formatting...

Range("T6:GQ186").Select
With Selection.Font
.Name = "Calibri"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With

Range("T6:GQ186").Select
Selection.NumberFormat = "0"

Range("A1:B1").Select ' Unselects the model region and places cursor in upper left-hand corner

' ------------------------------------------------------------------

Application.ScreenUpdating = True

MsgBox ("The model is complete")

Exit Sub

ErrHandler:
MsgBox (Err)
Application.ScreenUpdating = True

End Sub

Re: More than 100 If statement alternative

Abhineo — Thu, 01 Oct 2020 15:32:54 GMT

Did code like this for each 356 days

Set Day1ML = $(Day1M);
Set Day2ML =$(Day1ML)+$(Day2M);
Set Day3ML = $(Day2ML)+ $(Day3M);

Set Day1M = ($(Planned_Total_Loaded)*$(pct1))/100;
Set Day2M = ($(Planned_Total_Loaded2)*$(pct2))/100;
Set Day3M = ($(Planned_Total_Loaded3)*$(pct3))/100;

Set Planned_Daily_Loaded = IF((Target_VIN_Load_Volume)> (Daily_VIN_Load_Capacity), (Daily_VIN_Load_Capacity),(Target_VIN_Load_Volume));
set Planned_Total_Loaded = (Planned_Daily_Loaded);
set Planned_Daily_Loaded2 = IF(((Planned_Total_Loaded) + (Daily_VIN_Load_Capacity)) <= (Daily_VIN_Load_Capacity),(Daily_VIN_Load_Capacity),((Target_VIN_Load_Volume)-(Planned_Daily_Loaded)));
set Planned_Total_Loaded2 = (Planned_Total_Loaded) + (Planned_Daily_Loaded2);
set Planned_Daily_Loaded3 = IF(((Planned_Total_Loaded2) + (Daily_VIN_Load_Capacity)) <= (Daily_VIN_Load_Capacity),(Daily_VIN_Load_Capacity),((Target_VIN_Load_Volume)-(Planned_Total_Loaded2)));
set Planned_Total_Loaded3 = (Planned_Total_Loaded2) + (Planned_Daily_Loaded3);

Re: More than 100 If statement alternative

Kushal_Chawda — Thu, 01 Oct 2020 16:08:50 GMT

@Abhineo Calculation could be done in script level to simplify the expression. I will try to formulate.

Re: More than 100 If statement alternative

Abhineo — Thu, 01 Oct 2020 16:14:20 GMT

Thanks @Kushal_Chawda . Appreciate your quick response.

Re: More than 100 If statement alternative

Kushal_Chawda — Thu, 01 Oct 2020 21:46:22 GMT

@Abhineo try below. I have assumed the data field names.

Data: LOAD Day, // Date or day number Pct, // stores $(pct1).... $(pct365).. values Target_VIN_Load_Volume, Daily_VIN_Load_Capacity FROM table; Final: load *, (Measure*Pct)/100 as [Percent] load *, if(rowno()=1, if(Target_VIN_Load_Volume> Daily_VIN_Load_Capacity,Daily_VIN_Load_Capacity,Target_VIN_Load_Volume), rangesum(peek(Measure),if(peek(Measure)+Daily_VIN_Load_Capacity<=Daily_VIN_Load_Capacity, Daily_VIN_Load_Capacity,Target_VIN_Load_Volume-peek(Measure))) ) as Measure Resident Data order by Day; drop table Data;

Re: More than 100 If statement alternative

Abhineo — Wed, 07 Oct 2020 09:18:49 GMT

Excellent Kush, Its pretty close and thanks for same. Input are little different and in millions, also model_start_date matching required so, joined with date of input stream and in order with Daynum, script changed to using DayNum , instead Rowno(), so pretty close results are coming. Adding some stuffs to get exact figures. Your code was really helpful and tanks again.

I should get below final output:

Planned Total Complete
0
2
5
9
14
20
27
37
49
61
70
78
86
96
111
127
178
258
344
447
563
705
900
1115
1322
1503
1651
2067
2457
2788
3081
3307
3488
3646
3802
3954
4093
4217
4362
6183
8822
12319
17415
23161
28798
34292
40513
47316
54971
64405
76526
91682
109781
128808
147419
166983
189598
216082
246062
275944
302511
326890
346056
360813
373081
383948
393927
403613
413501
425069
439979
458572
479814
504337
530648
557619
586287
617578
652452
691744
735117
772854
811127
851801
894740
939223
983476
1026403
1065998
1101558
1134395
1165062
1193504
1220019
1244664
1267535
1288710
1308880
1328216
1346943
1365465
1383932
1401883
1419228
1437573
1457662
1479058
1501176
1531680
1562191
1590127
1616488
1640647
1662139
1684352
1709538
1734400
1757654
1777605
1794163
1808201
1820748
1832173
1842720
1852635
1862014
1870886
1879407
1887713
1895750
1903500
1911019
1918276
1925264
1932052
1938654
1945052
1951241
1957304
1963233
1968996
1974565
1980038
1985368
1990548
1995586
2000562
2005343
2009955
2014443
2018816
2022996
2027068
2030988
2034737
2038318
2041793
2045099
2048311
2051389
2054336
2057082
2059656
2062174
2064588
2066869
2069055
2071139
2073121
2074995
2076805
2078557
2080258
2081850
2083376
2084815
2086222
2087558
2088828
2090043
2091222
2092345
2093394
2094393
2095370
2096209
2096988
2097732
2098434
2099088
2099687
2100242
2100733
2101184
2101599
2101998
2102374
2102719
2103022
2103309
2103589
2103862
2104127
2104388
2104638
2104872
2105097
2105317
2105538
2105758
2105978
2106198
2106418
2106638
2106858
2107078
2107298
2107519
2107739
2107957
2108176
2108393
2108608
2108823
2109037
2109250
2109462
2109673
2109882
2110088
2110292
2110492
2110687
2110879
2111068
2111254
2111435
2111611
2111780
2111946
2112111
2112272
2112434
2112595
2112756
2112917
2113076
2113236
2113394
2113550
2113702
2113851
2113996
2114136
2114273
2114405
2114533
2114656
2114771
2114880
2114981
2115081
2115175
2115261
2115341
2115415
2115483
2115547
2115607
2115665
2115720
2115772
2115822
2115871
2115918
2115963
2116008
2116051
2116093
2116133
2116172
2116210
2116247
2116282
2116316
2116346
2116374
2116400
2116418
2116433
2116447
2116458
2116468
2116477
2116483
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485
2116485

for each day Input load is:

1
12
9
10
13
11
17
34
25
8
2
8
18
26
38
31
271
246
119
240
223
373
545
375
219
122
77
2215
86
136
150
144
109
77
156
168
83
43
309
12357
6498
9025
15100
10948
7178
7184
13987
12931
15168
22302
31150
36471
39127
29118
23623
34707
50132
57001
57237
36435
18606
26398
11
14
3590
6907
5908
7458
10220
21271
33750
38780
36331
46687
40661
38016
46572
55014
64315
73312
76125
12357
60889
68057
70884
60882
52159
48851
34384
24697
27735
25810
20311
17573
16552
13724
11307
13173
12336
12356
14037
14850
11579
10513
21917
26879
25287
22605
81367
24616
15340
19450
18664
9894
27190
41112
20704
12642
13554
12612
10625
14751
16336
12832
10445
14014
14833
13469
14633
14197
10553
7490
11486
10912
8966
5883
8724
8382
6519
6319
5206
6050
10416
8368
6880
5054
5157
5732
4666
4371
4837
4341
3577
3742
3303
4158
3420
3487
3212
2640
2986
2503
1508
2421
2609
2215
1657
2147
2039
1366
2051
2018
2105
1829
2530
2353
2367
2384
2678
2517
2202
2895
3073
2844
2660
2772
2025
1847
2244
2176
2120
1984
1732
1866
1934
1933
1762
1859
1639
1703
1857
1694
1789
1510
1299
1377
1333
1254
1069
1187
1063
1170
1010
1118
1048
940
1126
1063
1064
988
1071
1041
904
1012
1011
939
920
1044
922
803
987
969
902
812
848
726
772
803
781
698
700
673
642
513
723
520
2198
2262
552
525
719
442
729
423
439
2273
2201
15977
16834
16703
62113
7950
1893
259
1040
551
232
241
226
221
243
209
189
193
209
197
211
516
15618
408
387
208
173
644
1156
1091
23482
1615
190
163
1373
276
218
269
221
211
194
222
2782
228
198
67629
55631
272
89824
343
1624
1319
46741
61780
383
1335
367
298
273
336
71416
312
337
338
5817
319
322
310
268
776
275
248
302
279
267
261
298
267
227
261
257
251
35473
311
310
227
288
291
310
289
276
217
209
225
250
276
257
280
218
170
524
903
231
222
599
213
190
223
238
199