topic Flattening The String in Qlik Replicate

Flattening The String

AXK0121B — Thu, 02 May 2024 13:21:35 GMT

When we load data through Qlik replicate, In Source we have a fields containing multiple values, Seperated by ;

For Example,

Field Name in source: Tax

Value : 10;12;4

in target we want it to be flattened,

Tax1 field should have the value 10

Tax2 field should have the value 12

Tax3 field should have the value 4

We have written a expression and its working, is there any other simple maner if we can solve this issue, giving the logic we are using currently.

Tax1 :

substr($TAX,1,instr($TAX, ";")-1)

Tax 2:
substr(substr($TAX,instr($TAX, ";")+1,length($TAX)),1,
instr(substr($TAX,instr($TAX, ";")+1,length($TAX)), ";")-1)

Tax 3:
substr(substr($TAX,instr($TAX, ";")+1,length($TAX)),
instr(substr($TAX,instr($TAX, ";")+1,length($TAX)), ";")+1,length($TAX))

Re: Flattening The String

john_wang — Thu, 02 May 2024 13:28:49 GMT

Hello @AXK0121B ,

Not sure what's the target DB types. if it's possible we might define a VIEW in target side to separate the single column to multiple columns to simplify the task design and improve the replication performance.

Hope this helps.

John.

Re: Flattening The String

Heinvandenheuvel — Thu, 02 May 2024 15:51:45 GMT

@AXK0121B I think that is the best, and most readable you can do.

One minor tweak: The length argument (Z) in SUBSTR is optional, returning as much as there is if omitted.

Therefor you can slightly simplify using

substr(substr($TAX,instr($TAX, ";")+1),
instr(substr($TAX,instr($TAX, ";")+1), ";")+1)

instead of

substr(substr($TAX,instr($TAX, ";")+1,length($TAX)),
instr(substr($TAX,instr($TAX, ";")+1,length($TAX)), ";")+1,length($TAX))

Yes I tested - in the designer.

Hein.

Re: Flattening The String

AXK0121B — Thu, 02 May 2024 16:34:56 GMT

Thanks, Its working and Code looks good as well now.

Issue is that we have string with 5 values so when we are trying to flatten them in this manner the code is getting bigger and complex, i was checking if there is any other options as well, However the suggestion you provided helped in reducing the complexity.

Thanks @Heinvandenheuvel

Re: Flattening The String

Heinvandenheuvel — Thu, 02 May 2024 16:45:17 GMT

Indeed "the code is getting bigger and complex" when going beyond 3 or so segments.

Just now there was an other topic seemingly in this - if the OP was indeed talking about Replicate.

https://community.qlik.com/t5/Qlik-Replicate/How-to-handle-arrays-for-a-column-in-qlik-replicate/td-p/2447559

There I suggest a User Defined Transformation (UDT, C# code! ) might be in order.

Hein.