Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qlikmpate0
Contributor

Script utilization - need to be robust

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

Tags (2)
14 Replies

Re: Script utilization - need to be robust

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

qlikmpate0
Contributor

Re: Script utilization - need to be robust

Please find attached log file,

balrajahlawat
Esteemed Contributor

Re: Script utilization - need to be robust

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

qlikmpate0
Contributor

Re: Script utilization - need to be robust

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
Contributor

Re: Script utilization - need to be robust

How can i avoid that then? Sorry

Re: Script utilization - need to be robust

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

balrajahlawat
Esteemed Contributor

Re: Script utilization - need to be robust

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

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
Contributor

Re: Script utilization - need to be robust

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

Community Browser