Discussion Board for collaboration related to QlikView App Development.
The below condition consumes a long time in data reload,
WHILE Num ([Original Planned Delivery Date] + Iterno ()) <= Num (Today ())
any suggestions are welcome to reduce the load time.
Thanks in advance.
no one can say i didnt ask for it.
from your script, my understanding is that you apply some business rules around punctuality but at the end of the day you want to count factory dates past the delivery date (not including delivery date) up to today. hence you have the applymap returning 1 for each factory date satisfying the date check.
i would say that doing teh applymap was very creative. i would do it a diff way. i would create a temporary table with factory id and delivery dates, join it with all factory dates. then trim this using teh date check (Factory Date > delivery date and factorydate <=today)
then aggregate the table by factory id and delivery date count factorydate
left join this to the table the count of factory date is the diff
this is just an alternative way to doing the same logic that you were doing using while function
the attached shows sample QVW as there is no way to get the factory dates from your QVW.
i would modify the following part of your script:
[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 ());
[Sales Document Item Key],
Sum ([Date Type]) as [Delivery Difference Days]
GROUP BY [Sales Document Item Key];
Thanks for your valuable reply. But unfortunately, I am unable to understand the apply map logic. I think that can be useful for limited data sets only.
I have re-attached the file of the original sample data with the original while condition.
Could you please do the needful?
@deepakqlikview_123 can you try running the script by commenting below piece of code. Sometimes aggregation in load script also takes a while to finish. If this is the case then avoid doing aggregation in load script and let the aggregation happen in front end.
//LOAD [Sales Document Item Key],
//Sum ([Date Type]) as [Delivery Difference Days]
the point is to remove the applymap and iteration and use table operations. i suggest one thing, add an exit script after the binary load and attach the reloaded qvw. this is the state prior to the iteration and i will apply the logic i am suggesting and see if that will work for you. pls add @edwin to your response so i can get notified
Hi, i didnt attach any QVW, i was hoping you can attach a different version where you didnt do the apply map logic. as i said, if you can stop the script right after the binary load, i will implement the logic i described in my earlier post.
add the statement
rt after the binary load in the first tab of your script. then reload and attach the reloaded qvw. this will allow me to implement the logic i described to your original data model
pls see attached. i had to add more data to Factory Calendar as there is only one order for CB, i added data for CN.
i added random delivery dates as original data didnt have any dates. just comment those out.
otherwise rest of the code should work