topic Re: Critical or toxic function combinations in QlikView

Critical or toxic function combinations

cdss-developer — Wed, 01 Aug 2018 07:51:24 GMT

Hi, I hope someone can help me here.

Critical or Toxic function combinations

I have a table with function (codes) per user that looks like this:

User	Function
Jim	AA169
Jim	GB123
Jim	GB546
Jack	AA169
Jack	AA180
Jack	AA171
Jack	TC654
Jason	AT335
Jason	AT336
Sarah	AA169
Sarah	AA170
Joey	AA170
Joey	TC111
Joey	TC113
Joey	TC555
Richard	AZ456

I also have a table (Excel) with critical or ‘toxic’ (functioncode) combinations that are not allowed or critical and that I want to identify to look at.

ID	Comb1	Comb2	Comb3	Comb4	Comb5	Comb6	Comb7	Comb8	Comb9	Comb10
1	GB123	GB546
2	AA170
3	AA170	TC555
4	QC456	QC457	QC458	DF458

(A combination of functioncodes can be toxic/ critical, but also a single code as you can see by ID2)

I would like to get a table that looks like this:

User	Toxic	ToxicID	ToxicHIT	ToxicCode(s)
Jim	Yes	1	GB123	GB123, GB546
Jim	Yes	1	GB546	GB123, GB546
Jack	No
Jason	No
Sarah	Yes	2	AA170	AA170
Joey	Yes	3	AA170	AA170, TC555
Joey	Yes	3	TC555	AA170, TC555
Richard	No

I would be greatly helped if someone could solve this for me.

Kind regards,

Eelco

Re: Critical or toxic function combinations

Gysbert_Wassenaar — Wed, 01 Aug 2018 08:54:41 GMT

Perhaps like this:

ToxicExcelTable:

CROSSTABLE (CombiID,Value)

LOAD * FROM ToxicExcelFile (ooxml, ...etc);

mapToxicList:

MAPPING LOAD concat(Value, '|', Value) as List, ID RESIDENT ToxicExcelTable GROUP BY ID;

UserFunction:

LOAD User, Function FROM ....;

UserFunctionList:

LOAD

Applymap('mapToxicList',FunctionList,Null()) as ToxicID

;

LOAD

User,

concat(Function, '|', Function) as FunctionList

RESIDENT

UserFunction;

Re: Critical or toxic function combinations

Peter_Cammaert — Wed, 01 Aug 2018 09:17:29 GMT

A suggestion for a script and data model:

From the Excel toxic/critical combination data, create a table with a composite string key (composed of the alphabetically sorted codes separated by a vertical bar or another character) and the corresponding ID. You'll get something like:

Key ID

GB123|GB546 1

AA170 2

AA170|TC555 3

QC456|QC457|QC458|DF458 4

From the first list, create a table with field User and again a composite string key that contains each possible combination of Function values for a particular user (permutation). Sort the Function values alphabetically first. For user Jim, you should get something like:

User Key

Jim AA169

Jim GB123

Jim GB546

Jim AA169|GB123

Jim AA169|GB546

Jim GB123|GB546

Jim AA169|GB123|GB546

The two tables will link on field Key, and in this instance the 6th record for Jim will lead to ID 1. Drop all rows from the second table that don't link to the first table.

If you really want the ID duplicated for all individual component functions, then you may get a data explosion (I don't know how large your actual data set is) So if that requirement can be omitted you'll get a much more compact data model.

Re: Critical or toxic function combinations

Peter_Cammaert — Wed, 01 Aug 2018 09:27:45 GMT

Ah, maybe also include all partial "Function" combinations? And add a GROUP BY to the last LOAD...

Re: Critical or toxic function combinations

cdss-developer — Wed, 01 Aug 2018 09:33:32 GMT

Thanks for your quick response.

First part (crosstable) is working, but I'm getting an Script Error at the mapping part.

And I do not understand what is wrong.

Script Error:

Invalid expression

mapToxicList:

MAPPING LOAD concat(Value, '|', Value) as List, ID RESIDENT ToxicExcelTable

Re: Critical or toxic function combinations

Gysbert_Wassenaar — Wed, 01 Aug 2018 09:36:18 GMT

Yeah, that mapping loads needs a group by

MAPPING LOAD concat(Value, '|', Value) as List, ID RESIDENT ToxicExcelTable GROUP BY ID

Re: Critical or toxic function combinations

Gysbert_Wassenaar — Wed, 01 Aug 2018 09:39:03 GMT

Maybe, but I reckoned every combination would be listed on a separate row.

Thanks for pointing out the group by error. fixed above.

Re: Critical or toxic function combinations

Peter_Cammaert — Wed, 01 Aug 2018 10:00:43 GMT

I wasn't very clear, I admit

What I mean is this. Joey has four Functions in the first table, but only one combination of two of them (the first and the last) will match a Toxic/Critical combination entry in the second table. AFAIK your code only matches Function combinations that include all Functions values listed. Which means that the resulting table would contain a single entry for Sarah. Am I right?

Re: Critical or toxic function combinations

Gysbert_Wassenaar — Wed, 01 Aug 2018 10:58:23 GMT

You're absolutely correct.

Re: Critical or toxic function combinations

Peter_Cammaert — Wed, 01 Aug 2018 11:20:58 GMT

However, this technique won't avoid Joey being listed with ID = 2 too. And that doesn't seem to be part of your requirement.

Re: Critical or toxic function combinations

cdss-developer — Wed, 01 Aug 2018 11:22:27 GMT

Thank you Gysbert and Peter,

Sorry, but I'm not getting any further than this. Can you help me again?

Combinations:

LOAD * INLINE [

ID, Comb1, Comb2, Comb3, Comb4, Comb5, Comb6, Comb7, Comb8, Comb9, Comb10

1, GB123, GB546

2, AA170

3, AA170, TC555

4, QC456, QC457, QC458, DF458

];

Users:

LOAD * INLINE [

User, Function

Jim, AA169

Jim, GB123

Jim, GB546

Jack, AA169

Jack, AA180

Jack, AA171

Jack, TC654

Jason, AT335

Jason, AT336

Sarah, AA169

Sarah, AA170

Joey, AA170

Joey, TC111

Joey, TC113

Joey, TC555

Richard, AZ456

];

ToxicExcelTable:

CROSSTABLE (CombiID,Value)

LOAD *

RESIDENT Combinations;

//FROM

//.\Combinaties.xlsx

//(ooxml, embedded labels, table is Blad1);

mapToxicList:

MAPPING LOAD concat(Value, '|', Value) as List, ID RESIDENT ToxicExcelTable GROUP BY ID;

UserFunction:

LOAD User, Function Resident Users;

//FUNCTIONS_GROUPED_BY_USER;

UserFunctionList:

LOAD

Applymap('mapToxicList',FunctionList,Null()) as ToxicID

;

LOAD

User,

concat(Function, '|', Function) as FunctionList

RESIDENT

UserFunction;

Re: Critical or toxic function combinations

Peter_Cammaert — Wed, 01 Aug 2018 11:55:43 GMT

Using your reference Table1 (Combinations) and Table2 (UserFunctions), I get as far as this:

I'm using this hack (BTW thanks Sunny, for the simple CrossTable trick to create value permutations):

TempIDCombination:
CROSSTABLE (ICColumn, ICValue)
LOAD *
RESIDENT Table1;


Translate2ID:
LOAD Concat(ICValue, '|') AS Key, ID
RESIDENT TempIDCombination
WHERE (len(trim(ICValue)) > 0)
GROUP BY ID;


DROP Table TempIDCombination;


RowCounts:
LOAD User, Count(DISTINCT Function) AS RowCount
RESIDENT Table2
GROUP BY User;


MaxRowCount:
LOAD Max(RowCount) AS MaxRowCount
RESIDENT RowCounts;


LET vMaxRowCount = peek('MaxRowCount');


DROP Tables RowCounts, MaxRowCount;


TempUF:
NOCONCATENATE
LOAD User, Function AS Function1 
RESIDENT Table2;


IF vMaxRowCount > 1 THEN
  FOR i = 2 TO vMaxRowCount
    LEFT JOIN (TempUF)
    LOAD User, Function AS Function$(i)
    RESIDENT Table2;
  NEXT
END IF


TempUF2:
CROSSTABLE (UFColumn, UFValue, 2)
LOAD RowNo() AS RowNo, *
RESIDENT TempUF;


LinkTable:
LOAD DISTINCT User, Key;
LOAD User, RowNo,
     Concat(DISTINCT UFValue, '|') AS Key
RESIDENT TempUF2
GROUP BY User, RowNo;

DROP Tables TempUF, TempUF2;

Re: Critical or toxic function combinations

cdss-developer — Wed, 01 Aug 2018 13:12:20 GMT

Thanks, this works for my test data,...

however (maybe I should have mentioned it) when I run this with the actual data (> 250000 user-function combinations), then it seems to get stuck. I have already tried to adjust this by saving the functiondata and tempfiles to QVDs, but unfortunately he gets stuck.

It gets stuck in this part of the code:

IF vMaxRowCount > 1 THEN

FOR i = 2 TO vMaxRowCount

LEFT JOIN (TempUF)

LOAD User, Function AS Function$(i)

RESIDENT Table2;

END IF

Does anyone have a suggestion to solve this performace issue?

Re: Critical or toxic function combinations

Gysbert_Wassenaar — Wed, 01 Aug 2018 13:38:08 GMT

Perhaps we should do this with bit masks and the bitand operator. Every function will need to be assigned a unique 'bit'. Every user gets a bit mask of the functions for that user. The toxic combinations get a similar bit mask. And then we bitand the user-function bit masks with the toxic-function bit masks. It the result is the same as the toxic bit mask we have a match. Something like this:

SET NullInterpret='';


Combinations:
CrossTable(CombId, Function)
LOAD * INLINE [
ID, Comb1, Comb2, Comb3, Comb4, Comb5, Comb6, Comb7, Comb8, Comb9, Comb10
1, GB123, GB546
2, AA170
3, AA170, TC555
4, QC456, QC457, QC458, DF458
];


Users:
LOAD * INLINE [
User, Function
Jim, AA169
Jim, GB123
Jim, GB546
Jack, AA169
Jack, AA180
Jack, AA171
Jack, TC654
Jason, AT335
Jason, AT336
Sarah, AA169
Sarah, AA170
Joey, AA170
Joey, TC111
Joey, TC113
Joey, TC555
Richard, AZ456
];


mapFunction2BitValues:
MAPPING LOAD 
 FieldValue('Function',RecNo()) as Function,
 pow(2,RecNo()) as FunctionBitValue
AutoGenerate FieldValueCount('Function');


tmpBitMasks:
LOAD
 User,
 sum(ApplyMap('mapFunction2BitValues',Function)) as UserFunctionBitmask
RESIDENT
 Users
GROUP BY
 User 
 ;

JOIN  (tmpBitMasks)


LOAD
 ID,
 concat(Function,',', Function) as ToxicFunctionList,
 sum(ApplyMap('mapFunction2BitValues',Function)) as ToxicBitmask
RESIDENT
 Combinations
GROUP BY
 ID
 ; 

ToxicResults:
LOAD
 User,
 ToxicID,
 ToxicFunctionList
WHERE
 MatchResult=0
 ; 
LOAD
 User,
 ID as ToxicID,
 ToxicFunctionList,
 UserFunctionBitmask bitand ToxicBitmask - ToxicBitmask as MatchResult
RESIDENT
 tmpBitMasks
 ;

DROP TABLE tmpBitMasks;

Re: Critical or toxic function combinations

Peter_Cammaert — Wed, 01 Aug 2018 14:11:04 GMT

Nice technique. It's just that I'm not sure you can create large enough bitmasks for a large nuber of different Function values...

Eelco, what's the number of different Function values in your data set?

Re: Critical or toxic function combinations

cdss-developer — Wed, 01 Aug 2018 14:12:53 GMT

This seems to work when I load inline (testdata), but when I load the real data, I get a script error:

Field not found - <Function>

JOIN (tmpBitMasks)

LOAD

ID,

concat(Function,',', Function) as ToxicFunctionList,

sum(ApplyMap('mapFunction2BitValues',Function)) as ToxicBitmask

RESIDENT

Combinations

GROUP BY

Re: Critical or toxic function combinations

Gysbert_Wassenaar — Wed, 01 Aug 2018 14:15:12 GMT

Yeah, that could be a problem. I reckoned it could work here. No idea how clever the Qix engine is regarding that. It might give up at 64 values.

I'm open to alternatives

Re: Critical or toxic function combinations

cdss-developer — Wed, 01 Aug 2018 14:21:50 GMT

I managed to reduce the USER - FUNCTION combination records from ±250000 to ±16000 by loading only the most critical functions.

Total distinct number of users: 1386

Total number of unique / distinct functions (application rights) = 2095

However, he continues to get stuck.

Re: Critical or toxic function combinations

cdss-developer — Wed, 01 Aug 2018 14:30:14 GMT

Have solved this (my fault), forgot to add this again: CrossTable(CombId, Function)

I will test now

Re: Critical or toxic function combinations

Gysbert_Wassenaar — Wed, 01 Aug 2018 14:30:57 GMT

Field not found means that there's no field with that name in the table your getting the data from. Field names are case sensitive so Function, FUNCTION and function are three different field names as far as Qlikview is concerned.

Total number of unique / distinct functions (application rights) = 2095

Ok, that likely won't work then until quantum computers become available.

Perhaps you can do it per user. What's the maximum number of distinct functions of the user with the maximum number of functions?