Simple math - number compression in qlikview

Tue, 25 Mar 2014 00:42:54 GMT

Please don't freak out. This question is small. I only added a lot of notes and sample tables to make it clear.

The following notes are taken from the book - Qlikview 11 for developers -

For example, if we have a measure that contains integer numbers ranging from 0 to 100,000,000, we can potentially end up with 100 million unique values. As an alternative, we could split the number into two 10,000 (the square root of 100 million) number ranges using the Div() and Mod() functions, shown here:

LOAD

Div(BigNumber, 10000) as BigNumber1,

Mod(BigNumber, 10000) as BigNumber2

FROM BigNumber;

The first expression in the preceding code performs an integer division of the value of BigNumber by 10,000. This means that only the resulting whole number is stored. The second expression performs a modulo operation, storing only the remainder of BigNumber divided by 10,000. Both of these fields have a potential of 10,000 unique values. That's 20,000 possible unique values when combined, or only 0.2 percent of the original list.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Question - This logic does not give me the correct values in qlik view. Why ?

File -

BigNumber

10,001;

10,002;

10,003;

11,0001;

12,0002;

13,0003;

Expected value as per math logic/SQL -

select BigNumber, BigNumber/10000 as Div, BigNumber%10000 as Mods

from BigNumber

BigNumber	Div	Mods
10001	1	1
10002	1	2
10003	1	3
110001	11	1
120002	12	2
130003	13	3

Qlikview output (imagine it as list boxes) -

BigNumber1 {1}

BigNumber2 {1,2,3}

Qlikview Script -

[BigData]:

LOAD

Div(BigNumber, 10000) as BigNumber1,

Mod(BigNumber, 10000) as BigNumber2

FROM

[..\..\..\..\Examples\BigNumberCompress.txt]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Why is my qlikview giving the wrong result ?

Re: Simple math - number compression in qlikview

Tue, 25 Mar 2014 00:51:20 GMT

It is working fine for me.

Thanks,

Ram

Re: Simple math - number compression in qlikview

Tue, 25 Mar 2014 00:54:49 GMT

I added it as a list box, whereas you added it as a table box. Why is the list box not showing the values correctly ?

Thanks.

Re: Simple math - number compression in qlikview

Tue, 25 Mar 2014 00:58:46 GMT

By the way, my data file has semi-colons at the end of each row, but yours does not.

Re: Re: Simple math - number compression in qlikview

Tue, 25 Mar 2014 01:45:14 GMT

there you go. Your format and list boxes.

Re: Simple math - number compression in qlikview

Tue, 25 Mar 2014 06:04:12 GMT

Hi John, Can you mark this thread as "Answered". Thanks.

topic Re: Simple math - number compression in qlikview in QlikView

Simple math - number compression in qlikview

Re: Simple math - number compression in qlikview

Re: Simple math - number compression in qlikview

Re: Simple math - number compression in qlikview

Re: Re: Simple math - number compression in qlikview

Re: Simple math - number compression in qlikview