topic Re: Script utilization - need to be robust in QlikView

Script utilization - need to be robust

qlikmpate0 — Thu, 05 Nov 2015 10:00:13 GMT

I have the following script which runs really slow, does anyone have any tips on making the load quicker?

OrderBookStatusTMP:

Load OE_Order_Lines_All.LINE_ID as [OBST Line Id],

[Order Quantity]

Resident Order_Line

where [Booked Flag] = 'Y'

and Status = 'AWAITING_SHIPPING';

Inner Join(OrderBookStatusTMP)

Load WSH_Delivery_Details.SOURCE_LINE_ID as [OBST Line Id],

WSH_Delivery_Details.DELIVERY_DETAIL_ID,

WSH_Delivery_Details.REQUESTED_QUANTITY

Resident WSH_DeliveryDetails

Where ApplyMap('HasAssignmentRecordMap',[Join WDA to WDD],'N') = 'N';

OrderBookStatus:

Load

WSH_Delivery_Details.DELIVERY_DETAIL_ID as [Join WDD to OBS],

//[Join OBS to Order Line],

//sum(OE_Order_Lines_All.PRICING_QUANTITY - OE_Order_Lines_All.CANCELLED_QUANTITY) as Quantity,

Sum(WSH_Delivery_Details.REQUESTED_QUANTITY) as Quantity,

'Order Units Booked' as [Order Sub Status],

'Booked' as [Order Status]

Resident

OrderBookStatusTMP

group by WSH_Delivery_Details.DELIVERY_DETAIL_ID;

drop table OrderBookStatusTMP;

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

Sum([PD Pickticket Quantity]) as Quantity,

'Released to Warehouse' as [Order Sub Status],

'Released to Warehouse' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] = '10'

group by [PD Detail Id];

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

Sum([PD Pickticket Quantity]) as Quantity,

'Waved' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] = '20'

group by [PD Detail Id];

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

Sum([PD Pickticket Quantity]) as Quantity,

'Picked' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] >= '21' and [PD PH Status] <= '34'

group by [PD Detail Id];

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

Sum([PD Pickticket Quantity]) as Quantity,

'In Packing' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] >= '35' and [PD PH Status] <= '40'

group by [PD Detail Id];

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

// If(Sum([PD Shipped Quantity]) = 0,

// If(sum([PD Packed Quantity]) = 0, sum([PD Pickticket Quantity]),sum([PD Packed Quantity]))

// ,sum([PD Shipped Quantity])) as Quantity,

Sum([PD State Quantity]) as Quantity,

'Loaded on Trailer' as [Order Sub Status],

'Loaded on Trailer' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] = '70'

group by [PD Detail Id];

Re: Script utilization - need to be robust

Anonymous — Thu, 05 Nov 2015 10:07:19 GMT

Could you share the log file from a reload to see what bits are slow.

Re: Script utilization - need to be robust

qlikmpate0 — Thu, 05 Nov 2015 10:11:08 GMT

Please find attached log file,

Re: Script utilization - need to be robust

Anonymous — Thu, 05 Nov 2015 10:12:36 GMT

Doing Resident and grouping again and again could be one cause of it.

Re: Script utilization - need to be robust

qlikmpate0 — Thu, 05 Nov 2015 10:13:09 GMT

Its this part of the code where it runs very slow:-

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

Sum([PD Pickticket Quantity]) as Quantity,

'Waved' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] = '20'

group by [PD Detail Id];

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

Sum([PD Pickticket Quantity]) as Quantity,

'Picked' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] >= '21' and [PD PH Status] <= '34'

group by [PD Detail Id];

Re: Script utilization - need to be robust

qlikmpate0 — Thu, 05 Nov 2015 10:14:15 GMT

How can i avoid that then? Sorry

Re: Script utilization - need to be robust

Anonymous — Thu, 05 Nov 2015 10:18:11 GMT

You could consider making sure all the loads from QVD's are Optimized loads.

Re: Script utilization - need to be robust

Anonymous — Thu, 05 Nov 2015 10:18:16 GMT

Avoid this grouping as you are taking Flags like:

Try this:

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

[PD Pickticket Quantity] as Quantity,

'Waved' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] = '20';

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

[PD Pickticket Quantity] as Quantity,

'Picked' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

Resident

PickTicket_Detail

where

[PD PH Status] >= '21' and [PD PH Status] <= '34';

Then at front end, you can simply use:

sum({<[Order Sub Status={'Waved'}]>} [PD Pickticket Quantity] )

Hope this will help!!

Re: Script utilization - need to be robust

tamilarasu — Thu, 05 Nov 2015 10:21:24 GMT

I would suggest you to store the PickTicket_Detail in qvd file. Then you can use concatenate like below. I'm sure it will be much faster.

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

Sum([PD Pickticket Quantity]) as Quantity,

'Waved' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

FROM

[PickTicket_Detail].qvd (qvd) where

[PD PH Status] = '20'

group by [PD Detail Id];

Concatenate(OrderBookStatus)

Load

[PD Detail Id] as [Join WDD to OBS],

Sum([PD Pickticket Quantity]) as Quantity,

'Picked' as [Order Sub Status],

'In Picking/Packing' as [Order Status]

FROM

[PickTicket_Detail].qvd (qvd)

where

[PD PH Status] >= '21' and [PD PH Status] <= '34'

group by [PD Detail Id];

Let me know.

Edit: Alter your code wherever you are using resident PickTicket_Detail.

Re: Script utilization - need to be robust

qlikmpate0 — Thu, 05 Nov 2015 10:27:44 GMT

That's the difficulty I'm having optimising the loads.

Re: Script utilization - need to be robust

tamilarasu — Thu, 05 Nov 2015 10:35:16 GMT

You have mentioned part of the code is causing the problem. Why not to store the table alone in QVD and use it Check my reply above.

Re: Script utilization - need to be robust

qlikmpate0 — Thu, 05 Nov 2015 10:37:09 GMT

isn't storing the qvd the same as using the resident table?

Re: Script utilization - need to be robust

Anonymous — Thu, 05 Nov 2015 10:37:36 GMT

Agree with Tamil as well...

Compare to Resident, loading from QVD would be faster...

Re: Script utilization - need to be robust

qlikmpate0 — Thu, 05 Nov 2015 11:16:01 GMT

This change using the stored qvd is running the same time, no change, may have to use a different strategy. Only knocked 4mins off

see log attached

Re: Script utilization - need to be robust

Anonymous — Thu, 05 Nov 2015 11:18:31 GMT

Then try to avoid Grouping as I suggested earlier.

And do at front end.

Already shared with you.

It will surely help you to improve performance.