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

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]

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]

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]

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

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]

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]

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

[deepakqlikview_123]

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

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;