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

[deepakqlikview_123]

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

[deepakqlikview_123]

Hi,

Please find the attached sample QVW file for the same.

Thanks

[edwin]

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]

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]

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]

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]

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

[edwin]

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]

Hi Edwin,

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

Thanks,

[edwin]

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