Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH 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 Edwin,

After applying the changes in the original script. I found that it saves time but unfortunately, the record count is not matching.

Regards,

Deepak

 

 

edwin
Specialist III
Specialist III

im guessing you mean order deliveries table original and new record count after calculating the date difference dont match - is that correct? 

the load distinct and left join should guarantee that there are no duplications. 

can you share the QVW even with a reduced set where you observed records not matching?  you can do this by grouping by factory ID and comparing counts before and after.  select the factroty IDs where the counts dont match and reduce data set keeping possible values.

it would be helpful if you can provide the original albeit reduced data set prior to the calculation of the date diff similar to what you did when you added the last QVW

deepakqlikview_123
Specialist
Specialist
Author

Yes, the count of Delivery difference days is not matching. 

I am using the below mention modified part in the script.

 

NoConcatenate tmpDeliveries: load distinct [Original Factory Calendar], [Original Planned Delivery Date] Resident [Order Delivery];
inner join (tmpDeliveries) load [%Factory Calendar ID_IDENT] as [Original Factory Calendar], [Factory Date] Resident [FactoryCalendar] where [Factory Date Type]='Workday';
//aggregate - count number of factory work dates geater than delivery date <= today
NoConcatenate NewDeliveries: load [Original Factory Calendar], [Original Planned Delivery Date], count([Factory Date]) as DeliveryDiffDays resident tmpDeliveries
where [Factory Date]>[Original Planned Delivery Date] and [Factory Date]<=Today() group by [Original Factory Calendar], [Original Planned Delivery Date];

 

 

//join back to deliveries
left join ([Order Delivery]) load [Original Factory Calendar], [Original Planned Delivery Date], DeliveryDiffDays Resident NewDeliveries;

drop table tmpDeliveries, NewDeliveries;

I am trying to attach the sample data but the data volume is huge so I am unable to do it and in the limited data sets count of [Delivery Difference Days] is not visible.

 

Thanks

edwin
Specialist III
Specialist III

it will he hard to trouble shoot without seeing the data.  

when you say  count of Delivery difference days is not matching
you are referring to the computed values before and after are not matching.  not the number of rows in Order Delivery - rt?

in that case look for factories where there is a difference, select that factory and reduce the data - hopefully that will reduce the data to a size thats permitted by QLik for upload.

edwin
Specialist III
Specialist III

@deepakqlikview_123 

its too bad there is no notification when you respond.  one way would be to call attention to it using @edwin 

deepakqlikview_123
Specialist
Specialist
Author

Hi Ediwin,

PFA.

I have added the files with the original transformation and the new transformation as per your suggestion.

Here is a difference.

Thanks

 

edwin
Specialist III
Specialist III

i tried the code against test original2 and the record count is the same.  so its not the code i gave you.  it must be something else.

edwin
Specialist III
Specialist III

@deepakqlikview_123  did you see my last post?  im very curious what came out of this

deepakqlikview_123
Specialist
Specialist
Author

@edwin

Thanks for your valuable inputs. But whenever, I am trying your logic my cout for records are not matching.

I have attached the test files' original code and your code in the attachments.

I have also compared the record counts for the same.

Thanks

edwin
Specialist III
Specialist III

this is strange, like the last time i ran it, i loaded your file test_original2.qvw and ran my code on it i get exactly the same row count.

the only thing i can think of trying is get the source QVW.  can you attach the file

sd new_datamodel_punctuality_order_delivery.qvw?

BINARY [test_original2 (1).qvw];

SET DateFormat='YYYY-MM-DD';

drop table Test; 

NoConcatenate tmpDeliveries: load distinct [Original Factory Calendar], [Original Planned Delivery Date] Resident [Order Delivery];
inner join (tmpDeliveries) load [%Factory Calendar ID_IDENT] as [Original Factory Calendar], [Factory Date] Resident [FactoryCalendar] where [Factory Date Type]='Workday';
//aggregate - count number of factory work dates geater than delivery date <= today
NoConcatenate NewDeliveries: load [Original Factory Calendar], [Original Planned Delivery Date], count([Factory Date]) as DeliveryDiffDays resident tmpDeliveries 
where [Factory Date]>[Original Planned Delivery Date] and [Factory Date]<=Today() group by [Original Factory Calendar], [Original Planned Delivery Date];

//join back to deliveries
left join ([Order Delivery]) load [Original Factory Calendar], [Original Planned Delivery Date], DeliveryDiffDays Resident NewDeliveries;

drop table tmpDeliveries, NewDeliveries;

exit script;

 

edwin_0-1607694863521.png