topic Converting binary sequence to decimal in QlikView

Converting binary sequence to decimal

Thu, 14 Jan 2016 16:22:54 GMT

I am loading a file that contains a column holding a binary sequence (bits) and need to convert it to a decimal value. An example is the sequence: 000010111101011101111010 which converts to the decimal value 776058. The math to do this is:

0×2²³+0×2²²+0×2²¹+0×2²⁰+1×2¹⁹+0×2¹⁸+1×2¹⁷+1×2¹⁶+1×2¹⁵+1×2¹⁴+0×2¹³+1×2¹²+0×2¹¹+1×2¹⁰+1×2⁹+1×2⁸+0×2⁷+1×2⁶+1×2⁵+1×2⁴+1×2³+0×2²+1×2¹+0×2⁰ = 776058

...but, the length of the incoming decimal value may vary so, I don't want to hardcode anything. Ideally, if there was a function in Qlik or a way of parsing this string iteratively....

Likewise, I also have hexadecimal values that need to be converted into binary.

Any creative approaches would be appreciated.

Re: Converting binary sequence to decimal

sunny_talwar — Thu, 14 Jan 2016 20:16:48 GMT

Try this may be:

Table:

LOAD Binary,

Len(Binary) as Length;

LOAD * Inline [

Binary

000010111101011101111010

];

LET vLen = Peek('Length');

LET vBinary = Peek('Binary');

LET vNumber = 0;

FOR i = 0 to ($(vLen) - 1)

LET vCalc = (Mid($(vBinary), ($(i) + 1), 1) * pow(2, $(vLen) - ($(i)+1)));

LET vNumber = $(vCalc) + $(vNumber);

NEXT

Re: Converting binary sequence to decimal

Thu, 14 Jan 2016 22:06:34 GMT

Sunny - this works beautifully. What I am trying to do is to save the calculated decimal value for each row of my table so I can save the entire file as a QVD. Is there any way to save the calculated decimal result at the row level instead of saving the last calculated result to a variable? My input file has over 1M rows in it.

Thanks!

Phil

Re: Converting binary sequence to decimal

swuehl — Thu, 14 Jan 2016 22:38:57 GMT

I think it should be possible to interpret binary values using Num#(Binary, '(BIN)'), but I failed to do right now.

This should work:

Table:
LOAD Binary, Sum( Bit <<  Bitno) as Decimal
GROUP BY Binary;
LOAD Binary, Mid(Binary,Length-(Iterno()-1),1) as Bit, Iterno()-1 as Bitno
While Iterno() <= Length;
LOAD Binary,
    Len(Binary) as Length;
LOAD * Inline [
Binary
000010111101011101111010
];

Re: Converting binary sequence to decimal

petter — Thu, 14 Jan 2016 22:57:50 GMT

The reason it fails is that the number of digits in the binary string (or characters) is limited to 14 for some strange reason. So the way to circumvent this limitation is to chunk the conversion into multiples of 14 characters (digits).

Re: Converting binary sequence to decimal

sunny_talwar — Thu, 14 Jan 2016 22:58:27 GMT

Super Stefan

Re: Converting binary sequence to decimal

sunny_talwar — Thu, 14 Jan 2016 23:17:52 GMT

I don't know what you guys are talking about. The script seems to interpret longer than 14 number

No change to Stefan's script except for adding the 39 long binary number here.

Table:

LOAD Binary, Sum( Bit << Bitno) as Decimal

GROUP BY Binary;

LOAD Binary, Mid(Binary,Length-(Iterno()-1),1) as Bit, Iterno()-1 as Bitno

While Iterno() <= Length;

LOAD Binary,

Len(Binary) as Length;

LOAD * Inline [

Binary

000010111101011101111010

010101010101011110101101001100101010101

0101

];

Re: Converting binary sequence to decimal

rwunderlich — Thu, 14 Jan 2016 23:25:31 GMT

I think swuehl is on the right track with Num#(). But as Petter pointed out, there is a 14 digit input limit to Num#().

Something like this: Num(Num#(Binary, '(BIN)'))

works fine as long as you don't have more than 14 digits. Assuming no more than 28 digits you can do something like

,Num(

Num#(right(Binary,14),'(BIN)')

+ (alt(Num#(left(Binary, rangemax(0,len(Binary)-14)),'(BIN)'),0) <<14)

) as Decimal

I'm sure there is a simpler syntax.

-Rob

Re: Converting binary sequence to decimal

sunny_talwar — Thu, 14 Jan 2016 23:29:08 GMT

Rob -

I think I am unable to understand the issue here, but I just added a sample with 39 numbers sequence and it seems to have worked with Stefan's code. Are you guys talking about something else? I am sorry, if it I am missing something very simple here

Re: Converting binary sequence to decimal

swuehl — Fri, 15 Jan 2016 00:15:15 GMT

Sunny, I believe the discussion has focused on NUM#(Binary, '(BIN)') issues, it was not about my alternative solution.

It's kind of strange that the num#() function is not able to parse binary values with more than 14 digits (we know this limit e.g. when interpreting integer values, but it doesn't really make sense for binary values). Looks like a bug to me.

For the alternative, I've noticed that we need to care about duplicate Binary values, so grouping only by Binary would be careless:

Table:

LOAD ID, Binary, Sum( Bit << Bitno) as Decimal

GROUP BY ID, Binary;

LOAD ID, Binary, Mid(Binary,Length-(Iterno()-1),1) as Bit, Iterno()-1 as Bitno

While Iterno() <= Length;

LOAD Binary,

Len(Binary) as Length,

recno() as ID;

LOAD * Inline [

Binary

000010111101011101111010

];

Re: Converting binary sequence to decimal

sunny_talwar — Fri, 15 Jan 2016 00:21:16 GMT

Got it. So this was your alternative method. Thanks Stefan

Re: Converting binary sequence to decimal

jagan — Fri, 15 Jan 2016 01:05:55 GMT

HI,

Try like this

Decimal :

LOAD Binary, Sum(DecimalValue) as Decimal

GROUP BY Binary;

LOAD Binary,

Alt(Pow(If(Mid(Binary, IterNo(), 1) = 1, 2), Length-IterNo()), 0) as DecimalValue

While Iterno() <= Length;

LOAD

Binary,

Len(Binary) as Length

FROM DataSource;

STORE Decimal INTO Decimal.qvd;

Regards,

jagan.

Re: Converting binary sequence to decimal

Fri, 15 Jan 2016 01:24:28 GMT

Thanks to Sunny and swuehl for sparking the solutions...all of yours worked for the conversion just great but, for my very specific situation, Rob's was the one that worked out best.

Thanks Rob, Sunny, & swuehl!

Re: Converting binary sequence to decimal

petter — Fri, 15 Jan 2016 08:11:25 GMT

One solution using the splitting into 14 digits at a time could look like this:

Re: Converting binary sequence to decimal

hic — Fri, 22 Jan 2016 14:22:16 GMT

I have reported this as a bug.

And if I may, I would like to show a different solution to the problem:

First, create a mapping table that converts a four-digit BIN to HEX:

Bin2Hex:
Mapping Load
Right('0000' & Num(RecNo()-1,'(BIN)'),4) as BIN,
Num(RecNo()-1,'(HEX)') as HEX
Autogenerate 16;

Use this to convert the number to HEX, and then use the Num#() function:

Num(Num#(MapSubString('Bin2Hex',Right('0000' & Binary, 4*Ceil(Len(Binary)/4))),'(HEX)')) as Number,

The 14-digit limitation is still there, but now we talk about 14 HEX-digits. And that's slightly better....

HIC