Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Creator III

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

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:
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

@Year 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 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.

Labels (4)

• ### Script

23 Replies
Creator II

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:
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:
AutoGenerate(127);

// Note: Adjust if statement if going beyond 127
Temp1:
NoConcatenate
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:
Resident Temp1;

drop table Temp1;

exit Script;``````

Creator III
Author

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!

Creator II

@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;``````

Creator III
Author

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? 😅

Creator II

@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.

Employee

@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.

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.

Creator III
Author

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
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 🙂

MVP

another solution might be:

``````MapChangeType:
ChangeType, ChangeType1
0,'a'
1,'b'
2,'c'
4,'d'
8,'e'
16,'f'
32,'g'];

tabChanges:
ChangeType,
ApplyMap('MapChangeType',ChangeFlag) as Change
Where ChangeFlag;
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