Optimizing where clause in the scripting

[kishorj1982]

Hi,

Below is  a part of code cosumes a long time while reloading.

Test:
LOAD *,
Dual (If ([Delivery Difference Days] > 0,
If ([Delivery Difference Days] <= 5, 'Week or Less',
If ([Delivery Difference Days] <= 10, 'Two Weeks or Less',
If ([Delivery Difference Days] <= 20, 'Month or Less',
If ([Delivery Difference Days] <= 40, 'Two Months or Less', 'Over Two Months'))))),
If ([Delivery Difference Days] > 0,
If ([Delivery Difference Days] <= 5, 1,
If ([Delivery Difference Days] <= 10, 2,
If ([Delivery Difference Days] <= 20, 3,
If ([Delivery Difference Days] <= 40, 4, 5)))))) as [Late Status];
LOAD [Sales Document Item Key],
Sum ([Date Type]) as [Delivery Difference Days]
GROUP BY [Sales Document Item Key];
LOAD [Sales Document Item Key],
ApplyMap ('FactoryCalendar_map', [Original Factory Calendar] & '-' & Num ([Original Planned Delivery Date] + Iterno ()), 0) as [Date Type]
WHILE Num ([Original Planned Delivery Date] + Iterno ()) <= Num (Today ());
LOAD [Sales Document Item Key],
[Original Factory Calendar],
[Original Planned Delivery Date]
RESIDENT [Order Delivery]
WHERE Num ([Original Planned Delivery Date]) < Num (Today ()) AND (Isnull ([Comparison Date]) OR ([Delivered Quantity Punctuality] - [Original Confirmed Quantity]) < 0);

Could you please suggest how shall I go-ahead with the optimization? Any suggestions will be welcome.

Thanks

[ArnadoSandoval]

Hi @kishorj1982 

I attached a QFV files replacing your DUAL ( Nested IFs ) with DUAL ( ApplyMaps) it should improve your script performance; It was impossible to crack-down the rest of your script because my crystal-ball failed to work and you failed to explain your code. Please, in the future would you please elaborate in your problem, explain your code and include some sample data to work with it.

hth

[ArnadoSandoval]

Hi @kishorj1982 

Would you please elaborate on this While statement in your load script; It does not make much sense; what are you trying to achieve with it?

ApplyMap ('FactoryCalendar_map', [Original Factory Calendar] & '-' & Num ([Original Planned Delivery Date] + Iterno ()), 0) as [Date Type]
WHILE Num ([Original Planned Delivery Date] + Iterno ()) <= Num (Today ());
LOAD [Sales Document Item Key],
[Original Factory Calendar],

HTH.

[QFabian]

Hi @kishorj1982 , you should use intervalmatch() function to avoid the nested if's.

https://help.qlik.com/es-ES/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPre...