topic Remove special characters and split into rows in QlikView

Remove special characters and split into rows

surajap123 — Thu, 22 Jul 2021 14:31:36 GMT

Hi There,

I wanted to remove $ character in the below values. The $ character within the string represents different values, which should be moved into separate rows.

Table:
LOAD *
INLINE [
Key, Value
58, $London city$New York$
66, $Tokyo$New Delhi$
];

Output:

Key, Value
58, London city
58, New York
66, Tokyo
66, New Delhi

Please help

Re: Remove special characters and split into rows

Kushal_Chawda — Thu, 22 Jul 2021 20:10:06 GMT

@surajap123 try below

Table: load * where len(trim(Value))>0; LOAD Key, subfield(Value,'$') as Value FROM table;

Re: Remove special characters and split into rows

surajap123 — Thu, 22 Jul 2021 14:49:42 GMT

I tried it, seems it didn't work.

Please help.

Re: Remove special characters and split into rows

Kushal_Chawda — Thu, 22 Jul 2021 15:46:12 GMT

@surajap123 when you take a resident load if columns are same then it will autoconcatenate with previous table so add noconcatenate before you do resident load

NoConcatenate
load *
where len(trim(Value))>0;
LOAD Key,
subfield(Value,'$') as Value
Resident Table;

DROP table Table;

Re: Remove special characters and split into rows

surajap123 — Thu, 22 Jul 2021 19:02:26 GMT

Thank you so much @Kushal_Chawda