Skip to main content

I am using version 13.7 and trying to import two table that consists binary values in two different columns and as a part of check I need to check if the two columns have same values. However, there is no option to import binary values and they are getting imported as string by default. Please help me if there is a way to convert the values into binary or they can be converted back to their original binary values

 Hi, aiwalia

Ideas can be submitted via our Roadmap (Which can be accessed by customers)
 

Yes, there is a way to convert from a Binary value to a String value.

You will need an ASCII table, from which a Lookup Item is created for the Standard ASCII and Extended ASCII characters of your choice Character Encoding.

Please find attached a quick example demonstrating the conversion of a whole binary string into separate binary bytes, which are then converted to ASCII characters via a prepared lookup item and at the end concatenating the ASCII characters to return the String value.
 

Final Output

 

Previewing the data flow after splitting into individual binary bytes and looking up the ASCII character
File Structure of the attached example
Plan

 

Thank you Danny,
Can you help me understand how I can treat two columns having RAW datatype containing UUID values?
The use case is the same as above; we have to compare both the column values.

Hi aiwalia, good morning.
Thank you for the like on my ☝🏻 post 🤗.

Could you provide more detail on the use case for treating UUID values?

If you had the following two UUIDs, what are you trying to determine?

0168856d-99d6-470a-90fc-dabfcba13dd3
27b6297b-ed53-42ae-afd8-b11b6f5d22f0

Good Morning Danny,
Let me try to explain with an example
There are two tables- T1, T2
T1 has a column C1
T2 has a column C2
Where C1 and C2 have datatype RAW and UUID values.
Now, in the plan file, I need to check if any record in T1 is also present in T2 based on values of C1 and C2 but the two columns don’t match any records with = check as their values get encoded on import and type changes to STRING.

PS: the two columns do have values in data source table that should match

Hi aiwalia,

In a .plan in the ONE Desktop application, you can use the Representative Creator step to evaluate the condition of a value appearing in a given input (table).

Please find attached a quick example comparing 2 input tables each containing 500 UUIDs.
The plan provides 3 outputs

  1. summary of the distinct UUIDs and advises if the UUID exists in table 1 or 2 or both.
  2. new table 1 and table 2 files which are enriched with the summary information.

I hope this helps with your use case.

Plan file from Ataccama ONE Desktop. Comments have been included to help explain what each step in the process is doing.
Summary output, showing 3 new columns. Count of UUID and boolean 0/1 to denote if value is in that table.
Table 1 enriched with summary information.
Table 2 enriched with summary information.

 

Reply


Community Terms of Use & ConditionsCookie settings