topic Re: Permutations Math in QlikView

Permutations Math

richardouellett — Thu, 18 Jun 2015 12:56:57 GMT

I have created a field called 'Sequence' a 7 character (Upper Case) text field that is made up from the sequence (ascending order) of 7 timestamps that are captured for each record transaction. The timestamps are:

O = Ordered product

G = List product

L = Label Product

C = Collect

A = Arrive Product

P = Prepare Product

R = Report on Product

Depending on the sequence order they are performed a typical Sequence field could look like OGLCAPR or OLGCAPR. My analysis thus far is displayed below:

I now want to perform this time analysis above based on distinct combinations of 2 , i.e. Permutations without repetition (n=7, r=2)

List has 42 maximum entries.

OL OG OC OA OP OR LO LG LC LA LP LR GO GL GC GA GP GR CO CL CG CA CP CR AO AL AG AC AP AR PO PL PG PC PA PR RO RL RG RC RA RP

If I take the same table above and apply the MID() function to the Sequence filed I would get the results below.

Challenge/Question?

I now want to perform an analysis similar to the above on unique timestamp permutations for example 'OG' occurred 646 + 23 + 5 = 474 times.and PR occurred 905 times. (In the above example I want to analyze the performance of OG regardless of it being performed in step 1 or step 2.) The new analysis would consists of unique permutations, e.g., OG, PR, LO with corresponding Count, % and throughput statistics. Hope this all makes sense. How can this be accomplished in QlikView?

Re: Permutations Math

sunny_talwar — Thu, 18 Jun 2015 13:10:23 GMT

Wow this is some high end stuff you are doing here (at least seems like that). To tell you the truth I don't understand the question well, but would love to know what you have already done (see it in a qvw) and then may be try to understand what you are looking to do and may be propose a solution ultimately (there is no certainty that I will be able to provide a solution, but I would still love to give it a try )

If we are in agreement and if you can share the qvw of what you have done, we can get the ball rolling.

Best,

Sunny

Re: Permutations Math

swuehl — Thu, 18 Jun 2015 13:16:44 GMT

Maybe like

=COUNT({<Sequence = {"*OG*"}>} Sequence)

resp.

=COUNT({<Sequence = {"*PR*"}>} Sequence)

COUNT( IF(Sequence LIKE '*OG*', Sequence))

Re: Permutations Math

richardouellett — Thu, 18 Jun 2015 13:42:23 GMT

Thank you for this suggestion. This is a large database with many millions of records. The goal is to find a way to determine which of the 42 possible permutations had activity for a give query and then do the analysis. In the above example I believe there were only 18 of 42 permutations that had activity.

Re: Permutations Math

swuehl — Thu, 18 Jun 2015 13:50:53 GMT

You do already have a field with your permutations, right? Let's assume it's called PERMUT.

You can create a chart with PERMUT as dimension and an expression:

=COUNT(IF (Sequence LIKE '*' & PERMUT & '*', Sequence))

How does this perform in your setting? There are some alternatives for the match in the front end, you could also consider creating a link table between Sequence field and its permutations in the script.

Re: Permutations Math

richardouellett — Thu, 18 Jun 2015 13:56:40 GMT

The only field I have to work with is the 7 character Sequence field. Each successive 2 letter combination from this field provides the permutation equivalent, e.g., Mid(Sequence,1,2). I tried to show this in the last image above.

Re: Permutations Math

richardouellett — Thu, 18 Jun 2015 13:58:07 GMT

Hit return too fast. I like your second suggestion, can you provide a bit more detail?

Re: Permutations Math

swuehl — Thu, 18 Jun 2015 14:03:36 GMT

Richard Ouellette wrote:

The only field I have to work with is the 7 character Sequence field. Each successive 2 letter combination from this field provides the permutation equivalent, e.g., Mid(Sequence,1,2). I tried to show this in the last image above.

Well, I think you also have a list of permuations:

List has 42 maximum entries.

OL OG OC OA OP OR LO LG LC LA LP LR GO GL GC GA GP GR CO CL CG CA CP CR AO AL AG AC AP AR PO PL PG PC PA PR RO RL RG RC RA RP

Just create an INLINE LOAD for the list and you're set.

And you liked my second suggestion, which one?

edit:

This is how you can create your link table:

LOAD Sequence, mid(Sequence,iterno(),2) as Permut 
INLINE [
Sequence
OGLCAPR
OLGCAPR
] 
while IterNo() <= 6;

Instead of the INLINE, you can do a RESIDENT LOAD of your Sequence table.

Re: Permutations Math

richardouellett — Thu, 18 Jun 2015 18:50:05 GMT

Swuehl

Below is some sample code that you graciously assisted me with last week. As you can see I am calculating the time intervals as load time due to the complexity of the calculations and application impact. I would like to now perform the time durations based on the various time stamp permutations based on your above recommendation but am at a bit of a loss with how to proceed. Can you assist? Also my interval calculation is long and complex... is there an easier way to accomplish this?

DATA:

LOAD

recno() as ID,

Timestamp(today() + RAND()) as O,

Timestamp(today() + RAND()) as G,

Timestamp(today() + RAND()) as L,

Timestamp(today() + RAND()) as C,

Timestamp(today() + RAND()) as A,

Timestamp(today() + RAND()) as P,

Timestamp(today() + RAND()) as R

AutoGenerate 20;

CrossTable (Field, Time) LOAD * RESIDENT DATA;

LEFT JOIN (DATA)

LOAD

ID,

Concat(Field, '',Time) as Sequence

Resident X

Group BY ID;

drop table X;

Drop fields ID;

Store DATA into Data.qvd (qvd);

Drop Table DATA;

DATA:

Load O,

Sequence,

Interval((If( MID(Sequence,7,1)='O',O, If( MID(Sequence,7,1)='L',L, If( MID(Sequence,7,1)='G',G, If( MID(Sequence,7,1)='C',C,

If( MID(Sequence,7,1)='A',A, If( MID(Sequence,7,1)='P',P,R))))))) - (If( MID(Sequence,1,1)='O',O, If( MID(Sequence,1,1)='L',L,

If( MID(Sequence,1,1)='G',G, If( MID(Sequence,1,1)='C',C, If( MID(Sequence,1,1)='A',A, If( MID(Sequence,1,1)='P',P,R)))))))

, 'hh:mm')*24 as i1_7,

Interval((If( MID(Sequence,2,1)='O',O, If( MID(Sequence,2,1)='L',L, If( MID(Sequence,2,1)='G',G, If( MID(Sequence,2,1)='C',C,

If( MID(Sequence,2,1)='A',A, If( MID(Sequence,2,1)='P',P,R))))))) - (If( MID(Sequence,1,1)='O',O, If( MID(Sequence,1,1)='L',L,

If( MID(Sequence,1,1)='G',G, If( MID(Sequence,1,1)='C',C, If( MID(Sequence,1,1)='A',A, If( MID(Sequence,1,1)='P',P,R)))))))

, 'hh:mm')*24 as i1_2,

Interval((If( MID(Sequence,3,1)='O',O, If( MID(Sequence,3,1)='L',L, If( MID(Sequence,3,1)='G',G, If( MID(Sequence,3,1)='C',C,

If( MID(Sequence,3,1)='A',A, If( MID(Sequence,3,1)='P',P,R))))))) - (If( MID(Sequence,2,1)='O',O, If( MID(Sequence,2,1)='L',L,

If( MID(Sequence,2,1)='G',G, If( MID(Sequence,2,1)='C',C, If( MID(Sequence,2,1)='A',A, If( MID(Sequence,2,1)='P',P,R)))))))

, 'hh:mm')*24 as i2_3,

Interval((If( MID(Sequence,4,1)='O',O, If( MID(Sequence,4,1)='L',L, If( MID(Sequence,4,1)='G',G, If( MID(Sequence,4,1)='C',C,

If( MID(Sequence,4,1)='A',A, If( MID(Sequence,4,1)='P',P,R))))))) - (If( MID(Sequence,3,1)='O',O, If( MID(Sequence,3,1)='L',L,

If( MID(Sequence,3,1)='G',G, If( MID(Sequence,3,1)='C',C, If( MID(Sequence,3,1)='A',A, If( MID(Sequence,3,1)='P',P,R)))))))

, 'hh:mm')*24 as i3_4,

Interval((If( MID(Sequence,5,1)='O',O, If( MID(Sequence,5,1)='L',L, If( MID(Sequence,5,1)='G',G, If( MID(Sequence,5,1)='C',C,

If( MID(Sequence,5,1)='A',A, If( MID(Sequence,5,1)='P',P,R))))))) - (If( MID(Sequence,4,1)='O',O, If( MID(Sequence,4,1)='L',L,

If( MID(Sequence,4,1)='G',G, If( MID(Sequence,4,1)='C',C, If( MID(Sequence,4,1)='A',A, If( MID(Sequence,4,1)='P',P,R)))))))

, 'hh:mm')*24 as i4_5,

Interval((If( MID(Sequence,6,1)='O',O, If( MID(Sequence,6,1)='L',L, If( MID(Sequence,6,1)='G',G, If( MID(Sequence,6,1)='C',C,

If( MID(Sequence,6,1)='A',A, If( MID(Sequence,6,1)='P',P,R))))))) - (If( MID(Sequence,5,1)='O',O, If( MID(Sequence,5,1)='L',L,

If( MID(Sequence,5,1)='G',G, If( MID(Sequence,5,1)='C',C, If( MID(Sequence,5,1)='A',A, If( MID(Sequence,5,1)='P',P,R)))))))

, 'hh:mm')*24 as i5_6,

Interval((If( MID(Sequence,7,1)='O',O, If( MID(Sequence,7,1)='L',L, If( MID(Sequence,7,1)='G',G, If( MID(Sequence,7,1)='C',C,

If( MID(Sequence,7,1)='A',A, If( MID(Sequence,7,1)='P',P,R))))))) - (If( MID(Sequence,6,1)='O',O, If( MID(Sequence,6,1)='L',L,

If( MID(Sequence,6,1)='G',G, If( MID(Sequence,6,1)='C',C, If( MID(Sequence,6,1)='A',A, If( MID(Sequence,6,1)='P',P,R)))))))

, 'hh:mm')*24 as i6_7

FROM

Data.qvd (QVD);

Re: Permutations Math

swuehl — Thu, 18 Jun 2015 23:24:45 GMT

If I've understood correctly, I think your interval calculations can be simplified if you perform them same place you calculate your sequence:

DATA:
LOAD
  recno() as ID,
  Timestamp(today() + RAND()) as O,
  Timestamp(today() + RAND()) as G,
  Timestamp(today() + RAND()) as L,
  Timestamp(today() + RAND()) as C,
  Timestamp(today() + RAND()) as A,
  Timestamp(today() + RAND()) as P,
  Timestamp(today() + RAND()) as R
AutoGenerate 20;

X:
CrossTable (Field, Time) LOAD * RESIDENT DATA;

LEFT JOIN (DATA)
LOAD
  ID,
  Concat(Field, '',Time) as Sequence,
  (min(Time,7)-min(Time,1))*24 as i1_7,
  (min(Time,2)-min(Time,1))*24 as i1_2,
  (min(Time,3)-min(Time,2))*24 as i2_3,
  (min(Time,4)-min(Time,3))*24 as i3_4,
  (min(Time,5)-min(Time,4))*24 as i4_5,
  (min(Time,6)-min(Time,5))*24 as i5_6,
  (min(Time,7)-min(Time,6))*24 as i6_7   
Resident X
Group BY ID;

drop table X;

LOAD Sequence, mid(Sequence,IterNo(),2) as Permut, 1 as Count
RESIDENT DATA
WHILE iterno() <= 6;

I also added the link table to your permutations (and not shown here, an INLINE table of all permutations, see attached QVW).

Does this allow to perform the analysis you want to do?

Re: Permutations Math

MarcoWedel — Thu, 18 Jun 2015 23:51:00 GMT

Hi,

awesome solution.

Having millions of records it could improve the performance to use only distinct Sequence values in the link table:

LOAD DISTINCT Sequence, mid(Sequence,IterNo(),2) as Permut, 1 as Count

hope this helps

regards

Marco

Re: Permutations Math

swuehl — Fri, 19 Jun 2015 00:03:56 GMT

This might distort the aggregated count (well, as I've understood the requirements of the analysis).

But you can aggregate the counts:

LOAD Sequence, Permut, count(Sequence) as Count

GROUP BY Sequence, Permut;

LOAD Sequence, mid(Sequence,IterNo(),2) as Permut

RESIDENT DATA

WHILE iterno() <= 6;

Re: Permutations Math

richardouellett — Fri, 19 Jun 2015 11:17:02 GMT

Swuehl

You are amazing! The simplification of the interval formula and generating the Permut field is exactly what I am looking for this this analysis.

Below, I took the snippet of code you provided above (not the attached qvw) and with autogenerate set to 1 looked at a single record. There are 2 pieces to the analysis that I am doing:

Examining the order in which the steps are performed for each record and then calculating the time intervals. This was accomplished with the creation of the Sequence field and the interval fields i1_2… i6_7.
Generate the Permut fields for each transaction and then calculate the time intervals for each of these. So in the singe record generated below I would now need to calculate the time interval for AL, CG, GR, OC, PO and RA

Requirement 2 still poses a challenge for me. The main table that contain these transaction have over 9 million records and it is growing. The sequence field and the time interval fields can continue to remain in the main table as they now are. In order to perform the analysis on the Permut field, since there will be a 1 to many (6) relationship for each transaction record in the main table, will require a separate table that is created at LOAD time with the time intervals for each Permut per-calculated. I I envision that the Permut table will consist of 3 fields of information, the ID field that ties it back to the main DATA table, the Permut field and the calculated interval time for each Permut.

Does this approach make sense? If yes can you advise on the creation of the Permut table and the calculation for each time interval?

Re: Permutations Math

richardouellett — Fri, 19 Jun 2015 11:18:58 GMT

Marco, thank you for you suggestion. For this analysis Distinct can not be used.

Re: Permutations Math

richardouellett — Fri, 19 Jun 2015 11:20:56 GMT

Sunny, thanks code snippets shared below.

Re: Permutations Math

swuehl — Fri, 19 Jun 2015 11:37:14 GMT

Try this for link table generation:

LOAD ID, mid(Sequence,IterNo(),2) as Permut, 1 as Count,
pick(IterNo(), i1_2, i2_3, i3_4, i4_5, i5_6, i6_7) as PermutInterval
RESIDENT DATA
WHILE iterno() <= 6;

edit: to keep the number of distinct values low (this will also save time in the fact and link table), it might be a good idea to round the calculated intervals to a number of decimal places needed for your analysis.

Re: Permutations Math

richardouellett — Fri, 19 Jun 2015 11:57:32 GMT

Swuehi, you are a genius, that did the trick! Thanks so much for your help with this post!

Re: Permutations Math

richardouellett — Fri, 19 Jun 2015 12:05:23 GMT

Regarding..."round the calculated intervals to a number of decimal places needed for your analysis."

Rounding to 2 decimal places is all that is required... can this be accomplished in the script?

Re: Permutations Math

swuehl — Fri, 19 Jun 2015 12:12:36 GMT

Sure,

this code is rounding the intervals:

LEFT JOIN (DATA)

LOAD

ID,

Concat(Field, '',Time) as Sequence,

round((min(Time,7)-min(Time,1))*24,0.01) as i1_7,

round((min(Time,2)-min(Time,1))*24,0.01) as i1_2,

round((min(Time,3)-min(Time,2))*24,0.01) as i2_3,

round((min(Time,4)-min(Time,3))*24,0.01) as i3_4,

round((min(Time,5)-min(Time,4))*24,0.01) as i4_5,

round((min(Time,6)-min(Time,5))*24,0.01) as i5_6,

round((min(Time,7)-min(Time,6))*24,0.01) as i6_7

Resident X

Group BY ID;

Leaving the original timestamp fields untouched (though also these could be rounded or maybe even dropped?).

Re: Permutations Math

richardouellett — Fri, 19 Jun 2015 12:17:11 GMT

Thanks again! I have learned many things from you through this post!