Script utilization - need to be robust

[qlikmpate0]

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

[Anonymous]

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

[qlikmpate0]

Please find attached log file,

[Anonymous]

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

[qlikmpate0]

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

[qlikmpate0]

How can i avoid that then? Sorry

[Anonymous]

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

[Anonymous]

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

[tamilarasu]

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.

[qlikmpate0]

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