Solved: Critical or toxic function combinations - Page 2 - 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

cdss-developer · ‎2018-08-01

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;

Peter_Cammaert · ‎2018-08-01

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;

cdss-developer · ‎2018-08-01

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;

Does anyone have a suggestion to solve this performace issue?

Gysbert_Wassenaar · ‎2018-08-01

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;

talk is cheap, supply exceeds demand

Peter_Cammaert · ‎2018-08-01

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?

cdss-developer · ‎2018-08-01

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

ID

Gysbert_Wassenaar · ‎2018-08-01

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

talk is cheap, supply exceeds demand

cdss-developer · ‎2018-08-01

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.

cdss-developer · ‎2018-08-01

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

I will test now

Gysbert_Wassenaar · ‎2018-08-01

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?

talk is cheap, supply exceeds demand