Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

talentive
New Contributor II

How to optimize a loop/peek operation?

PROBLEM: I have a very slow running loop that peeks() at a bit mask field in each row in order to extract the values into a separate fact table

DETAILS:

I have a data model in which each possible status is encoded as a different bit.

Here would be the 'hard coded' table representing the possible bit values:

_CarTypeBitMask:

LOAD * INLINE [
_CarTypeMaskVal, _CarTypeMaskText
1,Truck

2,Car

4,Mini Van

8,Cross-Over

16,2-wheel

32,4-wheel

64,AWD

];


And the list of cars read in from a data source: (example made up, I'm not really dealing with cars)

[Cars]:
LOAD * INLINE [
%CarID,CarTypeMask
1,18          // Car + 2-wheel
2,33          // Truck + 4-wheel
3,24          // Cross-Over + 2-wheel
];

The idea is to generate a new table pulling out the car type combinations:


CarTypes:

%CarID,CarType

1,Car

1,2-Wheel

2,Truck

2,4-Wheel

3,Cross-Over

3,2-Wheel

Here's the script I run, but with thousands of rows it is way too slow:

// CREATE CarTypes 1-N table

//

vNumRows = NoOfRows('Cars')
FOR i=0 to $(vNumRows)-1;                                        // for each Car
    LET vCarID=peek('%CarID',$(i),'Cars');                    // read unique car ID

    LET vMask=peek('CarTypeMask',$(i),'Cars');          // read the mask

    FOR j = 1 toNoOfRows('_CarTypeBitMask')              // for each possible bit mask
        LET vMask_Num = peek('_CarTypeMaskVal',$(j),'_CarTypeBitMask');
        IF( $(vMask) bitand $(vMask_Num) = $(vMask_Num) ) THEN          // IF this bit is ON
            LET vCMF_Text = peek('_CarTypeMaskText',$(j),'_CarTypeBitMask');
            [CarTypes]:
            LOAD * INLINE[
                %CarID,CarType
                $(vCarID),$(vCMF_Text)]
;
        ENDIF
    NEXT

NEXT

(please note I modified the code here to be based on car and car types from my original code. My original code works. It's just slow)

So any ideas on how to improve the performance?

1 Solution

Accepted Solutions
MVP
MVP

Re: How to optimize a loop/peek operation?

Yes, Loops are really slow for what you are trying to do.

I would chose another approch, creating a load of your cars table with a integrated WHILE loop in the LOAD statement to generate a record per bit of the mask. Then MAP the bits:

CarTypeBitMask:

MAPPING

LOAD * INLINE [

_CarTypeMaskVal, _CarTypeMaskText

1,Truck

2,Car

4,Mini Van

8,Cross-Over

16,2-wheel

32,4-wheel

64,AWD

];

//And the list of cars read in from a data source: (example made up, I'm not really dealing with cars)

[Cars]:

LOAD * INLINE [

%CarID,CarTypeMask

1,18    

2,33    

3,24    

];

//

//The idea is to generate a new table pulling out the car type combinations:

//

//CarTypes:

//%CarID,CarType

//1,Car

//1,2-Wheel

//2,Truck

//2,4-Wheel

//3,Cross-Over

//3,2-Wheel

//

//

//Here's the script I run, but with thousands of rows it is way too slow:

//

// CREATE CarTypes 1-N table

//

RESULT:

LOAD *

  ,APPLYMAP('_CarTypeBitMask', Split, 'Oops, missing CarTypeBitMap entry') as CarType

WHERE Split;

LOAD %CarID,

  CarTypeMask bitand pow(2,iterno()-1) as Split

Resident Cars

WHILE iterno() < 7;

If this runs better, could you post the improvement?

7 Replies
MVP
MVP

Re: How to optimize a loop/peek operation?

Yes, Loops are really slow for what you are trying to do.

I would chose another approch, creating a load of your cars table with a integrated WHILE loop in the LOAD statement to generate a record per bit of the mask. Then MAP the bits:

CarTypeBitMask:

MAPPING

LOAD * INLINE [

_CarTypeMaskVal, _CarTypeMaskText

1,Truck

2,Car

4,Mini Van

8,Cross-Over

16,2-wheel

32,4-wheel

64,AWD

];

//And the list of cars read in from a data source: (example made up, I'm not really dealing with cars)

[Cars]:

LOAD * INLINE [

%CarID,CarTypeMask

1,18    

2,33    

3,24    

];

//

//The idea is to generate a new table pulling out the car type combinations:

//

//CarTypes:

//%CarID,CarType

//1,Car

//1,2-Wheel

//2,Truck

//2,4-Wheel

//3,Cross-Over

//3,2-Wheel

//

//

//Here's the script I run, but with thousands of rows it is way too slow:

//

// CREATE CarTypes 1-N table

//

RESULT:

LOAD *

  ,APPLYMAP('_CarTypeBitMask', Split, 'Oops, missing CarTypeBitMap entry') as CarType

WHERE Split;

LOAD %CarID,

  CarTypeMask bitand pow(2,iterno()-1) as Split

Resident Cars

WHILE iterno() < 7;

If this runs better, could you post the improvement?

talentive
New Contributor II

Re: How to optimize a loop/peek operation?

Thank you very much!

This is a very slick piece of code - I continue to forget that you can iterate during the load operation.

This is SIGNIFICANTLY FASTER!

I had a table of 30,000 entries and 16 possible bit masks.

The original script took 2 hours to run. With your solution it was down to 30 seconds !!

MVP
MVP

Re: How to optimize a loop/peek operation?

I had a table of 30,000 entries and 16 possible bit masks.

The original script took 2 hours to run. With your solution it was down to 30 seconds !!

Yes, that's what I call significant. There is a BIG overhead in For / Next loop and table creation.

Like it!

ashokvelisetty
New Contributor II

Re: How to optimize a loop/peek operation?

Hi Stefan,

I am also facing same type of problem. I am using Loops and Peek functions to get the Last team data in Ticket history table.Logic is working fine but it runs too slow and takes 10 hrs to complete the load.

    

Requirement is needs to pick most recent team details only per each ticket.

For more details please open attached excel where you can find sample Input and Output format.

Code i written in my application is below.

Ticket_History:

Load

    TCKT_HIS_KEY_AS_NUM,

    Ticket_His_TeamName,

    START_DATE_TIME,

    END_DATE_TIME,

    Ticket_His_OpenStatus_Desc,

    1 as Jink

//    TCKT_HIS_KEY_AS_NUM&Ticket_His_TeamName as TTnKey,

//    TCKT_HIS_KEY_AS_NUM&Ticket_His_TeamName&START_DATE_TIME&END_DATE_TIME as TempKey

   

Resident    Ticket_History1

Order by TCKT_HIS_KEY_AS_NUM,START_DATE_TIME desc , END_DATE_TIME desc;

DROP Table Ticket_History1;

Let vNoOfRows = NoOfRows('Ticket_History');

set vFlag=1;

FOR i=0 to $(vNoOfRows)-1

  Let i1= $(i)+1;

  Let vTicketKey2=Peek('TCKT_HIS_KEY_AS_NUM',$(i),'Ticket_History');

  Let vTicketKey3=Peek('TCKT_HIS_KEY_AS_NUM',$(i1),'Ticket_History');

 

  SET vTicketKey=Peek('TCKT_HIS_KEY_AS_NUM',$(i),'Ticket_History');

  SET vTicketKey1=Peek('TCKT_HIS_KEY_AS_NUM',$(i1),'Ticket_History'); 

  SET vTeam=Peek('Ticket_His_TeamName',$(i),'Ticket_History');

  SET vST=Peek('START_DATE_TIME',$(i),'Ticket_History');

  SET vET=Peek('END_DATE_TIME',$(i),'Ticket_History');

  SET vStatus=Peek('Ticket_His_OpenStatus_Desc',$(i),'Ticket_History');

  SET vKey=Peek('TCKT_HIS_KEY_AS_NUM',$(i),'Ticket_History')& Peek('Ticket_His_TeamName',$(i),'Ticket_History');

  SET vKey1=Peek('TCKT_HIS_KEY_AS_NUM',$(i1),'Ticket_History')& Peek('Ticket_His_TeamName',$(i1),'Ticket_History');

  Let vKey2=AutoNumber(Peek('TCKT_HIS_KEY_AS_NUM',$(i),'Ticket_History')& Peek('Ticket_His_TeamName',$(i),'Ticket_History'));

  lET vKey3=AutoNumber(Peek('TCKT_HIS_KEY_AS_NUM',$(i1),'Ticket_History')& Peek('Ticket_His_TeamName',$(i1),'Ticket_History'));

TestReplicate:

LOAD  $(vTicketKey) as Ticket,

      $(vTeam) as Team,

      $(vST) as ST,

      $(vET) as ET,

      $(vStatus) as Status,

      $(vKey) AS Key,

      $(vKey1) AS Key1,

      $(vFlag) as Flag

  AutoGenerate(1);

 

//  TRACE $(vKey2) = $(vKey3);

If '$(vKey2)'<>'$(vKey3)' then

set vFlag=0;

ENDIF

//TRACE $(vTicketKey2) = $(vTicketKey3);

If '$(vTicketKey2)'<>'$(vTicketKey3)' then

set vFlag=1;

ENDIF

//Where  $(vKey)=$(vKey1) ;

NEXT i;

@Stefan wuhl

Thanks in advance.

MVP
MVP

Re: How to optimize a loop/peek operation?

"Requirement is needs to pick most recent team details only per each ticket."

You should be able to fulfill your requirement by simply aggregating your table, grouped by ticket.

Use FirstSortedValue() to retrieve the latest information by a sort order value (latest timestamp).

Something like

LOAD

     Ticket,

     Max(Timestamp) as LatestTimestamp,

     FirstSortedValue(AnotherAttribute, -Timestamp) as LatestAnotherAttribute

RESIDENT YourTable

GROUP BY Ticket;

ashokvelisetty
New Contributor II

Re: How to optimize a loop/peek operation?

Hi,

Thanks for your quick reply on this.

Sorry for confusion, I don't want to pick only last record..I need to pick last team related all records.

If we use Aggregation we will get one record per Ticket, But i want to load last team all records to find Turntime of that last team

Please see below output.


     

Input
TCKT_HIS_KEY_AS_NUMSTART_DATE_TIMEEND_DATE_TIMETicket_His_OpenStatus_DescTicket_His_TeamName
14987915-06-17 10:3818-09-17 14:13OpenCS Team 8 Birm
14987918-09-17 14:1318-09-17 14:16In ProcessData Management 2
14987918-09-17 15:0418-09-17 15:09In ProcessCS Team 2 Birm
14987922-09-17 8:2427-09-17 8:44Customer ActionCS Team 2 Birm
14987927-09-17 8:4427-09-17 8:473rd Level SupportCS Team 2 Birm
14987927-09-17 8:4728-09-17 12:343rd Level SupportCS Team 8 Birm
14987928-09-17 12:3428-09-17 12:36In ProcessCS Team 8 Birm
14987905-10-17 12:0231-12-99 0:00CompleteCS Team 8 Birm
1731-05-17 12:4501-06-17 11:44OpenPost Live - Plus / Elite / Video
1701-06-17 11:4430-06-17 10:23Closed (in use)Post Live - Plus / Elite / Video
1730-06-17 10:2330-06-17 10:24In ProcessPost Live - Plus / Elite / Video
1730-06-17 10:2430-06-17 10:53In ProcessElite Support
1730-06-17 10:5331-12-99 0:00CompleteElite Support



    

Output
TCKT_HIS_KEY_AS_NUMSTART_DATE_TIMEEND_DATE_TIMETicket_His_OpenStatus_DescTicket_His_TeamName
14987927-09-17 8:4728-09-17 12:343rd Level SupportCS Team 8 Birm
14987928-09-17 12:3428-09-17 12:36In ProcessCS Team 8 Birm
14987905-10-17 12:0231-12-99 0:00CompleteCS Team 8 Birm
1730-06-17 10:2430-06-17 10:53In ProcessElite Support
1730-06-17 10:5331-12-99 0:00CompleteElite Support

Please let me know required more clarification on this.

Thanks,

Ashok.

MVP
MVP

Re: How to optimize a loop/peek operation?

Ok, but similar approach:

Find the last team per ticket, using my previous script. Then use these two fields, ticket and team, as key to join all records related to these ticket team combinations from your input table.

Community Browser