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

[ioannagr]

@MarcoWedel hello, thanks a lot for your contribution. I will try your script as well. Could you explain to me the where part in the preceding load ( where ChangeFlag;)  in TabChanges table. It's something I see for the first time and i would like to learn what it does.

Thank you in advance!!! 

[marcus_sommer]

For me it had worked:

[MarcoWedel]

Hi,

the "where ChangeFlag" bit is a where clause applied to the preceding load.
In this case it's filtering the rows that did have the corresponding ChangeType bit set.

[ioannagr]

Hello all 🙂 

I am back with a new case regarding this method. 

In this case, I have to take into account up to 2^28 in Temp 0. So Temp 0 and Temp1 are 200 million+  rows. Temp0 stores all data but Temp1 crashes at about 100M saying  "connection to qlik sense engine failed for unspecified reasons. Refresh your browser or contact your system administrator".  This happens at about 6 minutes of data loading. 

What does that mean? And how do I resolve this?

Update:

Now I got an error 16 :
Connection Lost. Make sure Qlik Engine is running properly. If your session has timed out due to inactivity, refresh to continue working.

[marcus_sommer]

For me it's not clear does it happens by loading the data or within the UI and what exactly are you doing there?

[ioannagr]

Hi @marcus_sommer 🙂 

It happens during data load.

My code is like below:
MapReason:
Mapping load * inline [
Reason, Reason1
0,'a'
1,'b.'
2,'c'
4,'d'
8,'e'
16,'f'
32,'g'
64,'h'
128,'i'
256,'j'
512,'k'
1024,'L'
2048,'m'
4096,'n'
8192,'o'
16384,'p'
32768,'q'
65536,'r'
131072,'s'
262144,'t'
524288,'u'
1048576,'v'
2097152,'w'
4194304,'x'
8388608,'y'
16777216,'z'
33554432,' another reason' 
67108864,'another reason2'
134217728,'another reason3'
];

Temp0:
LOAD RecNo()-1 AS A
AutoGenerate(268435456); //This generates numbers from 0 to 268435456

Temp1:
NoConcatenate
Load A,
if (A=0, ApplyMap('MapReason', 0) & '+') &
if (A bitand pow(2,0) >0, ApplyMap('MapReason', A bitand pow(2,0)) & '+') &
if (A bitand pow(2,1) >0, ApplyMap('MapReason', A bitand pow(2,1)) & '+') &
if (A bitand pow(2,2) >0, ApplyMap('MapReason', A bitand pow(2,2)) & '+') &
if (A bitand pow(2,3) >0, ApplyMap('MapReason', A bitand pow(2,3)) & '+') &
if (A bitand pow(2,4) >0, ApplyMap('MapReason', A bitand pow(2,4)) & '+') &
if (A bitand pow(2,5) >0, ApplyMap('MapReason', A bitand pow(2,5)) & '+') &
if (A bitand pow(2,6) >0, ApplyMap('MaReason', A bitand pow(2,6)) & '+') &
if (A bitand pow(2,7) >0, ApplyMap('MapReason', A bitand pow(2,7)) & '+') &
if (A bitand pow(2,8) >0, ApplyMap('MapReason', A bitand pow(2,8)) & '+') &
if (A bitand pow(2,9) >0, ApplyMap('MapReason', A bitand pow(2,9)) & '+') &
if (A bitand pow(2,10) >0, ApplyMap('MapReason',A bitand pow(2,10)) & '+') &
if (A bitand pow(2,11) >0, ApplyMap('MapReason',A bitand pow(2,11)) & '+') &
if (A bitand pow(2,12) >0, ApplyMap('MapReason',A bitand pow(2,12)) & '+') &
if (A bitand pow(2,13) >0, ApplyMap('MapReason',A bitand pow(2,13)) & '+') &
if (A bitand pow(2,14) >0, ApplyMap('MapReason',A bitand pow(2,14)) & '+') &
if (A bitand pow(2,15) >0, ApplyMap('MapReason',A bitand pow(2,15)) & '+') &
if (A bitand pow(2,16) >0, ApplyMap('MapReason',A bitand pow(2,16)) & '+') &
if (A bitand pow(2,17) >0, ApplyMap('MapReason',A bitand pow(2,17)) & '+') &
if (A bitand pow(2,18) >0, ApplyMap('MapReason',A bitand pow(2,18)) & '+') &
if (A bitand pow(2,19) >0, ApplyMap('MapReason',A bitand pow(2,19)) & '+') &
if (A bitand pow(2,20) >0, ApplyMap('MapReason',A bitand pow(2,20)) & '+') &
if (A bitand pow(2,21) >0, ApplyMap('MapReason',A bitand pow(2,21)) & '+') &
if (A bitand pow(2,22) >0, ApplyMap('MapReason',A bitand pow(2,22)) & '+') &
if (A bitand pow(2,23) >0, ApplyMap('MapReason',A bitand pow(2,23)) & '+') &
if (A bitand pow(2,24) >0, ApplyMap('MapReason',A bitand pow(2,24)) & '+') &
if (A bitand pow(2,25) >0, ApplyMap('MapReason',A bitand pow(2,25)) & '+') &
if (A bitand pow(2,26) >0, ApplyMap('MaptReason',A bitand pow(2,26)) & '+') &
if (A bitand pow(2,27) >0, ApplyMap('MapReason',A bitand pow(2,27)) & '+') AS ChangeType

Resident Temp0;
Drop table Temp0;
//Build Mapping Table after dropping trailing '+'
MapChangeType:
Mapping load A,Left(ChangeType, Len(ChangeType)-1) as ChangeType
Resident Temp1;
noconcatenate

MainTable:

LOAD *, A, 
ApplyMap('MapChangbeType',A,null()) as "Reason";

SQL select  *,A from Table;

drop table Temp1;

it crashes in the middle of loading Temp1 so I never get to create the qvd I need.

What should i do?

[marcus_sommer]

I believe the reason is quite simple - your environment has't enough resources for this task - at least doing it in this way. The main aspect is the RAM consumption of creating 268 M of distinct field-values - as numbers and as (larger) strings and also the table-index gets this lengths.

I think the final consumption couldn't be simply minimized but the creation-task could be sliced. This might be done with a loop-approach and each iteration is stored as qvd - and those n qvd's are later merged into a single qvd which is then used for the mapping-table. I could imagine that it might be then applicable by 64+ GB of available RAM.

Such loop-approach might go in this direction (take it just as ideas):

for i = 0 to 10
   load recno() as RecNo, rowno() as RowNo, $(i) as I ,
           pow(2, $(i)) as A, pow(2, $(i)) - pow(2, $(i) - 1) as B, pow(2, $(i) - 1) + recno() - 1 as C
   autogenerate pow(2, $(i)) - pow(2, $(i) - 1);
next

The single qvd's may hint already to the needed RAM and you should also try to merge them to see the final size. If your environment isn't suitable for it you should consider to split the sql also into n slices in regard to the lengths of the lookup to be able to apply the appropriate sub-mapping.  

[ioannagr]

The problem begins before getting to the sql part. In fact, I believe the sql table "Main Table" shouldn't be a problem because it now holds about 10M rows.

@marcus_sommer , so you suggest splitting the Temp0 table into chunks? Temp0 loads just fine and with a very nice speed as well, it's the resident Load of Temp1 that creates the problem. Also can you clarify how this splitting in Temp0 that (if i got it correctly) doubles the number of rows compared to the previous table.each time will help?

Thank you in advance 🙂

[Oleg_Troyansky]

Hi,

I have a feeling that some of the suggested solutions are a bit over-engineered. If all you need is to count the number of "change types", or in other words, the number of digits in the binary number that's corresponding to the provided decimal number, then I'd suggest doing just that - convert your number to the binary format and count the length of the binary number. The Qlik formula is quite simple, actually:

len(num(ChangeType, '(bin)')) 

The function num() with the format '(bin)' converts the number to the binary format, and the function len() counts the digits in the binary result.

Cheers,

[ioannagr]

Hello @Oleg_Troyansky! I want to present for each change, all its subtypes. For example for change 10 i want to show 8 + 2 as 10=2^3 +2^1, for change 11 i want to show 8 + 2 + 1  as 11 = 2^3 + 2^1 +2^0, etc.