Flattening The String

[AXK0121B]

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

[john_wang]

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.

[Heinvandenheuvel]

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

[AXK0121B]

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 

[Heinvandenheuvel]

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

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

Hein.