enumeration case with powers of two and "bitwise &" case

[ioannagr]

Hello all, so there's this challenge this time.

I was given this enumeration for the field "ChangeType", which stores 0,1, then powers of 2 like : 0,1, 2, 4, ,8, 16, 32, ... etc. The thing is the db table creator uses this enumeration like this- If they enter number 25 then there are three change types f & e & b  because 25=16+8+1, if they enter number 10 there are two changes e & c because 10=8+2.  So for example a person with change type 10 should be counted twice, once for reason 8 and once for reason 2. All I want is to count each person as many times as there are change types for them.

MapChangeType:
Mapping load * inline [
ChangeType, ChangeType1
0,'a'
1,'b.'
2,'c.'
4,'d'
8,'e'
16,'f'
32,'g'];

I contacted the db guy and they told me they wrote an sql script with bitwise operator &  like 

CREATE PROCEDURE [dbo].[sp_Updates]

@Year INT,

@UpdateSource INT

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO UpdateTable

SELECT @Year, @UpdateSource, 8, COUNT(*) AS e

FROM  Table2

WHERE PreviousYear < @Year

AND Year = @Year

AND (@UpdateSource IS NULL OR UpdateSource = @UpdateSource)

AND ChangeType & 8 > 0

END

How could I do the equivalent for Qlik Sense?

This is a much needed step for my further analysis and to be honest, I have no clue what to do so your help will be enormous.

Thank you in advance

[pravinboniface]

Since I am not aware of any way to loop through the bits in a field, this is a possible roundabout way of doing it.  This works until 127.  For higher values, please update the if statement as needed.

// Map powers of 2 to alphabets
CharMap:
Mapping load * inline [
I, Char
0,'a'
1,'b'
2,'c'
4,'d'
8,'e'
16,'f'
32,'g'
];

// Handle upt0 127, ie. we can decompose any number upto 127 to the sum of powers of 2
Temp0:
LOAD RecNo() AS I
AutoGenerate(127);

// Note: Adjust if statement if going beyond 127
Temp1:
NoConcatenate
Load I, 
if (I bitand pow(2,0) >0, ApplyMap('CharMap', I bitand pow(2,0)) & '+')  & 
if (I bitand pow(2,1) >0, ApplyMap('CharMap', I bitand pow(2,1)) & '+')  & 
if (I bitand pow(2,2) >0, ApplyMap('CharMap', I bitand pow(2,2)) & '+')  & 
if (I bitand pow(2,3) >0, ApplyMap('CharMap', I bitand pow(2,3)) & '+')  & 
if (I bitand pow(2,4) >0, ApplyMap('CharMap', I bitand pow(2,4)) & '+')  & 
if (I bitand pow(2,5) >0, ApplyMap('CharMap', I bitand pow(2,5)) & '+')  & 
if (I bitand pow(2,6) >0, ApplyMap('CharMap', I bitand pow(2,6)) & '+') AS ChangeType

Resident Temp0;

Drop table Temp0;

// Build Mapping Table after dropping trailing '+'
MapChangeType:
Mapping load I, left(ChangeType, len(ChangeType)-1) as ChangeType
Resident Temp1;

// Test with some data
TestData:
Load I, ApplyMap('MapChangeType',I)
Resident Temp1;

drop table Temp1;

exit Script;

[ioannagr]

Hello @pravinboniface , I really appreciate your input.

I would like an approach which would show like:

id changetype  Change    

1  10                   e

1  10                   c

2   8                    e

3   6                   d

3   6                   c

so when a user picks in a Change filter chart the option e   the count(id)=2 (when in fact they come from a different changetype 8 and 10)

In fact, I want to use ChangeType enumeration as an intermediate field for my final result:

id  Change    

1         e

1         c

2         e

3         d

3         c

Do you perhaps have any ideas on that?

I will look into your approach too though, for learning reasons! 

[pravinboniface]

@ioannagr Will something like this work for you?  Once you have the components, break them into it's own line.

load i, SubField(comp,'+') as change
inline [
i,comp
1,a+b
2,b+c
];
exit Script;

[ioannagr]

I have to work with powers up to 2^18, does this mean I will need to create components for every number from 1 to 524287? 😅

[pravinboniface]

@ioannagr Yes, it does sound tedious, but if you put it in the load script, it should be fine.  It seems tedious because I don't see a way to parse a string easily.  Otherwise, it's just a matter of converting the number into a binary string, locating which bit positions are '1' and then pow(2, pos).  Those will be the components you are looking for.

Hope that helps.

[Levi_Turner]

@pravinboniface is spot on. The thing that jumped out at me on review is that it's very odd to have an enum of 0. There's no way to determine whether changetype A was ever applied since adding 0 to any number is that number.

[marcus_sommer]

Here some ideas to create generic mapping tables to count the number of changes as well as to resolve the exact changes into a string-concatenation:

m1: mapping load recno(), chr(96 + recno()) autogenerate 26;
t0: load recno(), chr(96 + recno()) autogenerate 26;

t1:
load *, applymap('m1', IterNo * NoZero, '') as Char;
load *, len(replace(Bin, '0', '')) as #Change, -(B='1') as NoZero;
load *, mid(Bin, len(Bin) - iterno() + 1, 1) as B, iterno() as IterNo while iterno() <= len(Bin);
load recno() - 1 as Int, text(num(recno() - 1, '(bin)')) as Bin autogenerate 18;

t2: load Int, concat(Char, '+') as CharChain resident t1 where NoZero group by Int;
m2: mapping load * resident t2;

The t-tables could be dropped again - but they are useful to comprehend the logic especially if any further adaption is intended - or they might be used as dimension-tables within the data-model. 

[ioannagr]

Hello all

I sort of combined @pravinboniface 's approaches and I got my final result. @Levi_Turner to take 0 into account I added the line if (I=0, ApplyMap('MapChangeType', 0) & '+') &     to the following script, like :

// Note: Adjust if statement if going beyond 127
Temp1:
NoConcatenate
Load I, 
if (I=0, ApplyMap('CharType', 0) & '+') &
if (I bitand pow(2,0) >0, ApplyMap('CharMap', I bitand pow(2,0)) & '+')  & 
if (I bitand pow(2,1) >0, ApplyMap('CharMap', I bitand pow(2,1)) & '+')  & 
if (I bitand pow(2,2) >0, ApplyMap('CharMap', I bitand pow(2,2)) & '+')  & 
if (I bitand pow(2,3) >0, ApplyMap('CharMap', I bitand pow(2,3)) & '+')  & 
if (I bitand pow(2,4) >0, ApplyMap('CharMap', I bitand pow(2,4)) & '+')  & 
if (I bitand pow(2,5) >0, ApplyMap('CharMap', I bitand pow(2,5)) & '+')  & 
if (I bitand pow(2,6) >0, ApplyMap('CharMap', I bitand pow(2,6)) & '+') AS ChangeType

Resident Temp0;

 . @marcus_sommer  i tried your script and then connected my tables on Change field of my data, but I don't get the desired outcome.  I mean for change number 9 , i don't see 8 and 1 as addends of powers of 2. Am i missing something?

Thank you all so much for the contribution 🙂 

[MarcoWedel]

another solution might be:

MapChangeType:
Mapping load * inline [
ChangeType, ChangeType1
 0,'a'
 1,'b'
 2,'c'
 4,'d'
 8,'e'
16,'f'
32,'g'];

tabChanges:
LOAD id,
     ChangeType,
     ApplyMap('MapChangeType',ChangeFlag) as Change
Where ChangeFlag;
LOAD *,
     ChangeType bitand (1<<(IterNo()-1)) as ChangeFlag
Inline [
id,ChangeType
1,10
2,8
3,6
4,25
] (delimiter is ',')
While 1<<(IterNo()-1) <= ChangeType;

hope this helps

Marco