Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
deepakqlikview_123
Specialist
Specialist

iterno in where condition for date comparison consumes a long time to reload.

Hi,

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.

 

Regards,

Deepak

 

40 Replies
deepakqlikview_123
Specialist
Specialist
Author

Hi,

Please find the attached sample QVW file for the same.

Thanks

edwin
Specialist III
Specialist III

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:

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

NoConcatenate
Temp3:
LOAD
[Sales Document Item Key],
Sum ([Date Type]) as [Delivery Difference Days]
Resident Temp2
GROUP BY [Sales Document Item Key];

 

deepakqlikview_123
Specialist
Specialist
Author

Hi Edwin,

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?

Thanks,

 

Kushal_Chawda

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

 

deepakqlikview_123
Specialist
Specialist
Author

Hi Kushal,

The problem is with the while condition. I tried as per your suggestion but it seems that there is no difference.

Thanks

 

edwin
Specialist III
Specialist III

Hi,
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

deepakqlikview_123
Specialist
Specialist
Author

@edwin  I cannot find the attachment. Could you please do the needful?

edwin
Specialist III
Specialist III

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

exit script;

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 

deepakqlikview_123
Specialist
Specialist
Author

Hi Edwin,

Please find the attachment. I have attached the sample data for the same.

Thanks,

 

edwin
Specialist III
Specialist III

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