Converting binary sequence to decimal

[#1]

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.

[sunny_talwar]

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

[#3]

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

[swuehl]

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
];

[petter]

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

[sunny_talwar]

Super Stefan

[sunny_talwar]

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

]; 

[rwunderlich · Accepted solution]

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

[sunny_talwar]

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

[swuehl]

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

000010111101011101111010

];