Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.