split string into separate fields

[surajdhall]

Hi All,

I have single field in my table and want it into 3 fields. Please note that field3 can have values with pipe sometimes. Please help!

eg-

Field1    Field2       Field3

Asia      Oracle      HK|YY

//Script

Data:
LOAD * INLINE [
Key
Asia|Oracle|China
Asia|Oracle|Japan
Asia|Oracle|HK|XX
Asia|Oracle|HK|YY
Asia|Oracle|HK|ZZ
];

Thanks!

[tincholiver · Accepted solution]

Hi, here you have a posible solution:

for this load in your script:

LOAD
Data,
SubField(Data,'|',1)                                 as Field1,
SubField(Data,'|',2)                                 as Field2,
mid(Data,index(Data,'|',2)+1)             as Field3

[surajdhall]

Thanks for the quick response.

I see that you have used subfield function. I assume subfield effect the rows of my fact table, i might be wrong.

Can you help me without subfield function?

[tincholiver]

Sorry i dont´know another ay

[marcus_sommer]

The suggestion from  tincholiver will work. A subfield() with 3 parameters won't change the number of records - only the version with 2 parameters creates an internal table-loop with additionally records.

- Marcus