Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
edwin
Master II
Master II

row by row calculation in script is easy to maintain especially programmers have been used to seeing loops ad best used only in small data sets.  imagine a loop when to get to 10s of millions of records.

there are two changes i would recommend.  1st you can create a new table using the original table as resident and move your condition to the where clause (of course you would have to tweak this depending on your business rules) .  2nd dates are dual data type so you dont need to use the num function on both sides so you save on that as well.

one other thing you may want to look at/consider - are you loading from QVDs?  is there an opportunity to use QVDs? can you move this calculation to your ETL instead of QV?

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Hard to say without seeing the larger context of what you are trying to accomplish.  It looks like you are trying to generate rows between the delivery date and today. IntervalMatch() can be a faster alternative for this. 

-Rob

deepakqlikview_123
Specialist
Specialist
Author

Hi,

Below is the previous condition which consumes more time.

Temp1:
LOAD

[Sales Document Item Key],
[Original Factory Calendar],
[Original Planned Delivery Date]
Resident Temp
WHERE Num ([Original Planned Delivery Date]) < Num (Today ()) AND (Isnull ([Comparison Date]) OR ([Delivered Quantity Punctuality] - [Original Confirmed Quantity]) < 0); //Time

NoConcatenate

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 ());

 

This is the new condition,

Temp1:
LOAD

[Sales Document Item Key],
[Original Factory Calendar],
[Original Planned Delivery Date],
if (Num ([Original Planned Delivery Date] + Iterno ()) <= Num (Today ()),1, null()) as Flag
//RESIDENT [Order Delivery]
Resident Temp
WHERE Num ([Original Planned Delivery Date]) < Num (Today ()) AND (Isnull ([Comparison Date]) OR ([Delivered Quantity Punctuality] - [Original Confirmed Quantity]) < 0); //Time

NoConcatenate

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

 

The calculation after the creation of the flag works faster than the previous one.

 

Regards,

Deepak

edwin
Master II
Master II

"The calculation after the creation of the flag works faster than the previous one."

did your new code generate the expected number of rows?  doc says iterno() works only with a while clause

have you tried an outer join with the calendar and then filtering out what dates you do not need? 

deepakqlikview_123
Specialist
Specialist
Author

Hi,

A number of records after applying the above condition is not matching. Can you please suggest an example of how to move ahead on this part?

Because it consumes a long time to reload.

 

Thanks,

 

edwin
Master II
Master II

i myself am not sure what the design is about.  like what Rob said it appears you are creating a field for each row. but the applymap could be returning a completely different value from what anyone of us suspects.  if you can explain the objective that will help a lot.

of course an alternative to creating multiple rows of the same data for each day between two specific dates (assuming that is what you are after).  is to create a bridge which has all the business rules for the multiple manufactured dates (or field).  this way the fact table does not grow too big. 

for example if you want a date record generated between OrigianalPlannedDae and Today:

 

tmpBridge: load distinct OrigPlanDate resident FACTTABLE;
inner join (tmpBridge) load Date resident Calendar;
noconcatenate Bridge: load OrigPlannedDate, Date resident tmpBridge
where Date >= OrigPlannedDate and Date <=today();
drop table tmpBridge;

this will create a table between your fact table and the calendar table that has a row for each day between origplandate and today.  the advantage is you do not multiply your fact table rows.  of course the suggestion above is simplistic based on what i can see and you will have to tweak it to suit the rest of your data model.

as to the other conditions, you can either expand on the code to build those   business rules into your bridge table or move that calculation to your chart (which should be a last resort if you cant build it into your bridge)

hope that helps

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you explain in business data terms what you are trying to accomplish?

-Rob

deepakqlikview_123
Specialist
Specialist
Author

Hi Rob,

The script creates new rows to the data model based on the Planned Delivery Date to the current date.

But the iterno works only with while statement and comparison in the condition is needed

unfortunately, the while condition consumes a large reload time.

Below is the original code for the same.

Temp1:
LOAD

[Sales Document Item Key],
[Original Factory Calendar],
[Original Planned Delivery Date]
Resident Temp
WHERE Num ([Original Planned Delivery Date]) < Num (Today ()) AND (Isnull ([Comparison Date]) OR ([Delivered Quantity Punctuality] - [Original Confirmed Quantity]) < 0); //Time

NoConcatenate

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 ());

drop table Temp1;

Thanks,

 

 

edwin
Master II
Master II

can you share sample data especially from the FactoryCalendar map?