Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Debbie,
The letters above 'F' on one side of your join does appear something isn't being stored or converting to HEX.
If I was going to paraphrase your SQL side syntax, you are attempting something like this:
LOAD
RESULT_FIELD
FROM table1 T1, table2 T2
WHERE RAWTOHEX(T1.STORED_AS_RAW_FIELD) = T2.STORED_AS_HEX_FIELD
It is possible to perform the HEX & BINARY conversions on the QlikView side, and form key pairs on HEX values, or even raw binary patterns. Here's an example of a conversion in both directions RAW2HEX & HEX2RAW (<~these are not functions, just descriptive field names)
[RAW]:
LOAD * INLINE [
RAW
00000001
11110100
100101101100
11
1111111111111111
];
[RAW_TO_HEX]:
LOAD HEX_ID, CONCAT(HEX, ' ', RecNo()) AS RAW_SPACED, CONCAT(Num(Num#(HEX,'(BIN)'),'(HEX)'),'',RecNo()) AS RAW2HEX GROUP BY HEX_ID;
LOAD RecNo() AS HEX_ID, Num(Mid(RAW,IterNo()*4 - 3,4),'0000') AS HEX RESIDENT RAW
WHILE IterNo() <= RangeMax(1,Len(RAW)/4);
[HEX_TO_RAW]:
LOAD HEX_ID, CONCAT( Right('0000' & Num(Num#(RAW,'(HEX)'),'(BIN)'),4),'',RAW_ID) AS HEX2RAW GROUP BY HEX_ID;
LOAD HEX_ID, IterNo() AS RAW_ID, Mid(RAW2HEX,IterNo(),1) AS RAW RESIDENT [RAW_TO_HEX]
WHILE IterNo() <= Len(RAW2HEX);
Man, I couldn’t wait to rush home from work today (where I attempt to program) so I could get back on Community and talk more about RAW! THIS is Vegas!! 
Regardless of fixing these flawed HEX to BIN conversions that I offered up earlier, I am rethinking the whole approach of the RAW data type.
While Oracle appears to be taking in a stream of binary, not sure how that is done in QlikView.
My example ‘simulates’ interpreting binary, but actually using a long string of ‘1’ and ‘0’ text characters is probably an inefficient way to simulate RAW data. The symbol ‘0’ is ascii code 48 = 00110000 and ‘1’ is ascii code 49 = 00110001, so every ‘1’ or ‘0’ character supposedly representing 1 bit is still uses 8 bits to render its encapsulated data. So it is encoding at 1/8 efficiency.
Using num() values of 1,000,101, 1,100, etc.. is not the way to go either because binary doesn’t belong in a 10 radix system.
Hex seems like it naturally adds efficiency. For each HEX byte character that represents 4 bits of binary, it requires 8 bits to encapsulate the representing character. The store efficiency is ½. For every byte required to store a HEX character, you can encapsulate and retrieve 4 bits of data.
However, in the present (maybe there are some limitations I just don’t really understand), but in the present why not just consume whole bytes? Once we get out to Unicode possibilities maybe we can cover the whole 256 range in one character?
All the RAW data type really appears to be is a field that stores binary sequences the same way a text field would, but has a data-type name that tips you off that you aren’t to attempt making any sense from interpretation of the character combinations. The characters returned from a query of a RAW data-type field exist solely to encapsulate byte sequences.
The extended ASCII table seems like a natural choice to encapsulate our RAW data type, it offers binary coverage in exact byte-size, from 00000000 to 11111111. There doesn’t seem to be exclusive areas in terms of binary sequence possibilities when comparing a RAW field against an ASCII field, except that one is supposed to “make sense” and the other isn’t.
Attached is an example that seems to be having some initial positive results in encapsulating binary as a ‘TEXTRAW’ data type. There were 4 characters giving trouble re-converting back to binary when loading from the source .txt. These were 0 – Null, 10 – Line Feed, 12 – Form Feed, & 13 – Carriage Return. If anyone has any ideas how to parse them and extract their binary, the source file could be captured as extended ASCII. But in this case, the exceptions were moved to replacement characters above 255.
I think looking at the efficiency, the first 128 characters, less the 4 replacements will all encode at a 1:1 ratio. 1 character encapsulates and allows reading 8 bits of raw.
Then in the worst case scenario for efficiency at that point is the 4-replacements & the range from 128-255 all require 2-bytes to render. (If any of the characters above 128 could encode in 1-byte, this ratio would be even better)
- 124 characters at 8 bit encoding - 8 bit storage
- 132 characters at 8 bit encoding - 16 bit storage
HEX encoding of 256 characters
= encapsulates 1024 bits of data (256 x 4 bits)
= requires 2048 memory bits to store (256 x 8 bits)
= 1024 bits encoded / 2048 bits of memory to store = 50%
TEXTRAW encoding of 256 characters
= encapsulates 2048 bits (256 x 😎
= requires 3104 memory bits = (124 x 8 = 992) + (132 x 16 = 2112)
= 2048 bits encoded / 3104 bits of memory to store = 66%
(if it weren’t for those 4 characters giving trouble, this could’ve been 100%)
[HEX]:
LOAD HEX INLINE [
HEX
E5BE826A421AO8CBEFECF4YI9737303CF1A
];
[HEX_TO_RAW]:
LOAD *, PurgeChar(HEX2RAW_SplitByHEX,'|') AS HEX2RAW;
LOAD CONCAT( TEXT(Right('0000' & Num(Num#(RAW,'(HEX)'),'(BIN)'),4)),'|',RAW_ID) AS HEX2RAW_SplitByHEX;
LOAD IterNo() AS RAW_ID, Mid(HEX,IterNo(),1) AS RAW
RESIDENT [HEX]
WHILE IterNo() <= Len(HEX);
[INVALIDS]:
LOAD *, ORD + 256 AS REPLACE INLINE [
ORD
0
10
12
13
];
[MAP_INVALIDS]:
MAPPING LOAD ORD, REPLACE RESIDENT [INVALIDS];
[MAP_VALIDS]:
MAPPING LOAD REPLACE, ORD RESIDENT [INVALIDS];
Set Verbatim = 1;
[RAW_TO_STORAGE]:
LOAD CONCAT(BYTE,'|',BYTE_ID) AS HEX2RAW_SplitByBYTE,
CONCAT(
CHR( ApplyMap('MAP_INVALIDS', Num#(BYTE,'(BIN)') ) ) ,'',BYTE_ID) AS RAW2TEXT;
LOAD IterNo() AS BYTE_ID, Right(Repeat('0',8) & Mid(HEX2RAW,IterNo()*8-7,8),8) AS BYTE
RESIDENT [HEX_TO_RAW]
WHILE IterNo() <= RangeMax(1,Ceil(Len(HEX2RAW)/8));
[TEXT_TO_RAW]:
LOAD
RecNo() AS ROW,
[@1:n] AS TEXT_INCOMING
FROM
(
WHERE RecNo() = 1;
[TEXT_TO_RAW_CONVERSION]:
LOAD ROW, CONCAT(RAW,'|',RAW_ID) AS TEXT2RAW_ByByte,
CONCAT( Ord(Replace(RAW,chr(300),chr(00))) ,'|',RAW_ID) AS TEXT2RAW_Ord,
CONCAT( Mod(Ord(RAW),256) ,'|',RAW_ID) AS TEXT2RAW_Ord2,
CONCAT( Right('00000000' & Num(Mod(Ord(RAW),256),'(BIN)'),8),'|',RAW_ID) AS TEXT2RAW
GROUP BY ROW;
LOAD ROW, IterNo() AS RAW_ID, Mid(TEXT_INCOMING,IterNo(),1) AS RAW
RESIDENT [TEXT_TO_RAW]
WHILE IterNo() <= Len(TEXT_INCOMING);