Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cdss-developer
Contributor III
Contributor III

Critical or toxic function combinations

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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

27 Replies
Gysbert_Wassenaar

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
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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

cdss-developer
Contributor III
Contributor III
Author

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

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

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
Partner - Champion III
Partner - Champion III

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

You're absolutely correct.


talk is cheap, supply exceeds demand
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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