Solved: Re: Critical or toxic function combinations - Qlik Community

cdss-developer · ‎2018-08-01

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

Gysbert_Wassenaar · ‎2018-08-01

Another attempt using a loop over the toxic combinations. This should work fine I reckon. If you have an authorization matrix with no-go combinations of 64 functions or more there's a good chance you're doing something very wrong.

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


ToxicResults:
LOAD '' as User AutoGenerate 0;


For i = 1 To FieldValueCount('ID')


 tmpToxicCombination:
 LOAD
      ID, 
      Function as Function_Lookup
 RESIDENT
      Combinations
 WHERE
      ID = FieldValue('ID', $(i)) 
      ;

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

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

 JOIN  (tmpBitMasks)

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

 CONCATENATE (ToxicResults)

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

 DROP TABLES tmpToxicCombination, tmpBitMasks;

Next

talk is cheap, supply exceeds demand

View solution in original post

Gysbert_Wassenaar · ‎2018-08-01

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;

talk is cheap, supply exceeds demand

Peter_Cammaert · ‎2018-08-01

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.

Peter_Cammaert · ‎2018-08-01

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

cdss-developer · ‎2018-08-01

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

Gysbert_Wassenaar · ‎2018-08-01

Yeah, that mapping loads needs a group by

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

talk is cheap, supply exceeds demand

Gysbert_Wassenaar · ‎2018-08-01

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

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

talk is cheap, supply exceeds demand

Peter_Cammaert · ‎2018-08-01

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?

Gysbert_Wassenaar · ‎2018-08-01

You're absolutely correct.

talk is cheap, supply exceeds demand

Peter_Cammaert · ‎2018-08-01

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