How to handle arrays for a column in qlik replicate

[Snigdha174]

In source we have the data coming correct where as in target the array coulumns are occing as one column.

For example:

we have source columns as abc1,abc2, abc3, abc4, abc5 with values 19,0,0,2,4 in target data is loaded as abc with value 19;0;0;2;4.

For splitting each column I used below functions. please review.

abc1 : substr($abc1,1, instr($abc1,';')-1) = returns 1st value

abc2: SUBSTR($abc2, INSTR($abc2, ';') + 1 , INSTR($abc2, ';')-1) = doesnt return 2nd value instead returns 0;

Please help us in resolving this issue in qlik replicate.

Thanks in advance.

[Dana_Baldwin]

Hi @Snigdha174 

Did you work with Professional Services to set up the task? From our documentation:

The ODBC source endpoint can be used to access sources that are not included in Qlik Replicate’s extensive Support Matrix. However, unless enabled through Professional Services and approved by your Account Manager, the ODBC source endpoint should not be used. Additionally, the ODBC source endpoint should not be used to access any sources already listed in the Support Matrix.

Using ODBC to connect to a source | Qlik Replicate Help

The issue could be related to how we're reading the data from the source. You might try a different target endpoint type such as a file, to confirm that. Beyond that please work with our Professional Services team to get the task implemented and working properly. Your Account Manager can help get you in touch with them.

Thanks,

Dana

[DesmondWOO]

Hi @Snigdha174 ,

I would like to verify, is your question related to Qlik Replicate? Qlik Replicate is a tool to provide replication between endpoints or databases. Reading data from the target database and performing a transformation, it doesn't seem to be related to Replicate.

Regards,
Desmond

[Heinvandenheuvel]

@Snigdha174 . Welcome. Please be extremely clear about the source database.

Replicate transformation can NOT use calculated (transformed) columns as input. In your  ABC2 example that column does not even exist yet and will certainly not work. It can work with intermediary variable extracted from the original source.

It would be nice if INSTR had an extra argument for the ordinal number of the match - but it doesn't.

You might consider an enhancement requests for that, or for a new 'SEGMENT' (ELEMENT?) function.

Anyway, is the input per chance a CSV file? If so, just go through the steps to add a 'file' source,  define delimiter(s) for columns and rows. Now add a table, and under there add your columns and Replicate will parse it all out.

Is the input maybe a relational database with one of the columns containing a CSV list? The only know  non-coding solution for that is rather tedious as you have to 'mentally' extract substring from substrings from substrings to drill your way down to the last sub-fields. For a 3rd and find segment in a string with semicolon separators that looks like: substr(substr($X,instr($X, ";")+1), instr(substr($TAX,instr($X, ";")+1), ";")+1) 

Do you see how the the green section re-extracts the first section just to find the seconds separator? And that's just for 3.... it gets worse and worse for more and more elements.

This was recently discussed in the following topic: https://community.qlik.com/t5/Qlik-Replicate/Flattening-The-String/td-p/2447945

For high performance, deep list of sub-strings the only reasonable solution might be to write a UDT to extract 'segments' or 'elements' from a string based on delimiters. The Replicate installation comes with a UDT example. For Windows you can find that under C:\Program Files\Attunity\Replicate\addons . Brave souls should be able to figure out how to set this up (I did :-). Mere mortals may want to engage Qlik Professional Services to make sure to create a robust solution and to be aware of the latest  restrictions and features.

btw... seems to me only 'general question' label is appropriate at this file, perhaps functionality.

Hein.