Critical or toxic function combinations

[cdss-developer]

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

[Peter_Cammaert]

Indeed.

On-line converters usually limit input values to something like 9223372036854775807 which is the largest positive value in a 64-bit signed integer (first 63 bits set). I guess that's the maximum for QlikView too, before it starts converting bitmasks to floats.

It will work in Python, but that won't help us here.

[Peter_Cammaert]

If we can trust the num() function to go all the way, the situation is even more dramatic. Bit49 (starting from 0) is the last one that can be set, according to this screenshot:

But that could be because num() probably has limits of its own....

[Gysbert_Wassenaar · Accepted solution]

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

[cdss-developer]

Yes, this seems to work! 

Works in case for the combinations. When there is only one value, then not yet it seems. I will look forward to it tomorrow.

Thank you very much for all your help today !!

[Gysbert_Wassenaar]

With your example data it works with single values too. See Sarah in the attached example.

[cdss-developer]

Yes, you're right, it works

Perfect!

Thank you gwassenaar and pcammaert so much!

[MarcoWedel]

Hi,

although your thread already is answered I want to propose another solution:

tabUserFunct:
LOAD User,
    Concat('|'&Function&'|') as UserFunct
FROM [https://community.qlik.com/thread/309605] (html, codepage is 1252, embedded labels, table is @1)
Group By User;

tabToxComb:
CrossTable (CombNo, Function)
LOAD * FROM [https://community.qlik.com/thread/309605] (html, codepage is 1252, embedded labels, table is @2);

mapToxCombMsk:
Mapping
LOAD ID,
    '*'&Concat('|'&Function&'|','*')&'*' as ToxCombMsk
Resident tabToxComb
Where Len(Trim(Function))
Group By ID;

FOR Each vID in FieldValueList('ID')
   tabUserTox:
   LOAD *,
       SubField([ToxicCode(s)],', ') as ToxicHIT;
   LOAD User as UserTox,
       'Yes' as Toxic,
       '$(vID)' as ToxicID,
       TextBetween(Replace(ApplyMap('mapToxCombMsk','$(vID)'),'|*|',', '),'*|','|*') as [ToxicCode(s)]
   Resident tabUserFunct
   Where UserFunct like ApplyMap('mapToxCombMsk','$(vID)');
NEXT

Concatenate(tabUserTox)
LOAD User as UserTox,
    'No' as Toxic
Resident tabUserFunct
Where not Exists (UserTox, User);

DROP Tables tabToxComb, tabUserFunct;

RENAME Field UserTox to User;

hope this helps

regards

Marco

[cdss-developer]

Thanks Marco,

Nice solution!