topic Re: Parse string with fields in quotes in QlikView

Parse string with fields in quotes

Anonymous — Wed, 27 Jun 2018 06:20:25 GMT

Hi,

I have data of the form:

"Date1","Name1, Surname1","Item1","Otheritem1"

"Date2","Name2, Surname2","Item2","Otheritem2"

How do I get the data fields separated? I've tried 'subfield', but this caused Problems with the comma in the Name field. I know there is a 'textbetween' function, but I couldn't figure out how to use it to parse for all the fields in a row.

Thanks

Michael

Re: Parse string with fields in quotes

jayshrinipurte — Wed, 27 Jun 2018 06:28:41 GMT

Can you post expected output?

Re: Parse string with fields in quotes

Anonymous — Wed, 27 Jun 2018 06:29:19 GMT

And the data is not available as a Excel-file or a csv-file. It is generated temporarily within a script!

Re: Parse string with fields in quotes

Anonymous — Wed, 27 Jun 2018 06:35:03 GMT

Hi,

the Output should be like:

Dataset Data

1 Date1

1 Name1, Surname1

1 Item1

1 Otheritem1

2 Date2

2 Name2, Surname2

2 Item2

2 Otheritem2

Re: Parse string with fields in quotes

marcus_sommer — Wed, 27 Jun 2018 06:35:34 GMT

I think I would just use the comma as delimiter and concat the name and surname afterwards again.

- Marcus

Re: Parse string with fields in quotes

Ralf_Heukäufer — Wed, 27 Jun 2018 06:36:53 GMT

Hallo Michael,

was genau meinst du mit Problemen mit dem Komma im Namensfeld?

Sollen Name und Nachname nicht getrennt werden oder treten andere Probleme auf?

Re: Parse string with fields in quotes

Anonymous — Wed, 27 Jun 2018 06:38:19 GMT

Hallo Ralf,

ja genau, die beiden Namensfelder sollen nicht getrennt werden!

Re: Parse string with fields in quotes

Ralf_Heukäufer — Wed, 27 Jun 2018 06:43:34 GMT

Helptable:

Load

subfield(Textfeld,',1') as Date,

subfield(Textfeld,',2') as Name,

subfield(Textfeld,',3') as Nachname,

subfield(Textfeld,',4') as Item,

subfield(Textfeld,',5') as OtherItem

from....;

Haupttabelle:

Load

Date,

Name &' '& Nachname as Name,

Item,

OtherItem

resident Helptable;

Drop Table Helptable;

Re: Parse string with fields in quotes

marcus_sommer — Wed, 27 Jun 2018 07:33:36 GMT

Beside the approach of splitting the data by the comma and concat the needed results you could also adjust this part:

..."It is generated temporarily within a script"...

Another way might be to store these data into a csv and reading them again with the msq option in the file-format.

- Marcus

Re: Parse string with fields in quotes

Anonymous — Wed, 27 Jun 2018 07:48:23 GMT

I found a simple solution. just used SUBFIELD(Row,'",') to separate the fields.