topic Single quote inside columns in QlikView

Single quote inside columns

pauldamen — Mon, 20 Feb 2017 09:03:16 GMT

Hi all,

I have a CSV file which contains data in columns which start with a single quote. Looks like this:

ID Description Type Name

1 '17 name Main Name1

2 '17 name Main Name2

3 '17 name Main Name3

4 '17 name Main Name4

For some reason because of the quote it creates 1 field and my Description field looks like: '17 name;Main;Name1. My other fields move a column so the type field suddenly has the Name value in it.

I now it is because of the quotes because when I removed them from the data everything went fine. I tried to remove the quote with the Purgechar or replace and tried to tweak the CSV load with the msq or no quote value. Unfortunatly nothing seems to help.

Hope you guys have an idea how to fix this. Unfortunatly because of confidential data I can't attach the example

Regards, Paul

Re: Single quote inside columns

sravanthialuval — Mon, 20 Feb 2017 09:49:15 GMT

Hi Paul,

Try this

replace(Description, chr(39), '') as Description

It is working fine for me.

Re: Single quote inside columns

pauldamen — Mon, 20 Feb 2017 09:52:38 GMT

Hi Sravanthi,

I tried this as well, it does replace the character for me but I still get the same outcome. I still get the field with multiple columns in it.

Regards, Paul

Re: Single quote inside columns

Anil_Babu_Samineni — Mon, 20 Feb 2017 09:56:55 GMT

From here what was the expected result?

Re: Single quote inside columns

pauldamen — Mon, 20 Feb 2017 10:00:46 GMT

This is the result I got:

17 Odh buiten groenvoorziening;1160 OH van geb en t;1;Artikel;406071;Flora 17

Every ; is a different column, the ' in front off 17 has indeed been removed. But still it creates 1 field from multiple columns. The above should be 6 columns. This goes ok for all other fields except the one that start with a single quote

Regards Paul

Re: Single quote inside columns

marcus_sommer — Mon, 20 Feb 2017 10:09:24 GMT

You could split it with subfield() like:

subfield(FIELD, ';', 1) as F1,

subfield(FIELD, ';', 2) as F2,

....

- Marcus

Re: Single quote inside columns

Anil_Babu_Samineni — Mon, 20 Feb 2017 10:11:30 GMT

Still doubt yar

May be SubField() function can help you to split 6 columns. But i am not sure how you are interpreting the values

Re: Single quote inside columns

pauldamen — Mon, 20 Feb 2017 10:11:55 GMT

Hi,

Thing is that for the other 50000 records I already have the different columns. so only for about 100 records it does the above because there are quotes in there.

Regards, Paul

Re: Single quote inside columns

marcus_sommer — Mon, 20 Feb 2017 10:21:08 GMT

Do I understand it right that there just a few records which are different? Then I would probably load those data twice and filter those records per: where substringcount(FIELD, ';') = 0 respectively > 0 within the (concatenated) second load and perform there the subfield-stuff.

- Marcus

Re: Single quote inside columns

adamdavi3s — Mon, 20 Feb 2017 10:29:28 GMT

Can you share the csv, or at least a snippit of it?

I can't replicate this issue which suggests to me you might have another unprintable character in there.

What about trying

keepchar(field,'abcdefghijklmnopqrstuvwxyz0123456789') as field

Re: Single quote inside columns

pauldamen — Mon, 20 Feb 2017 10:46:49 GMT

I got a little bit further! With the subfield and the concatenate I got the 6 fields into there own column.

1 problem remaining; behind the 6 columns which are placed in one field I got 3 other columns which also have wrong values because they moved. This is not in the subfield, how can I get the correct values in these columns?

Re: Single quote inside columns

marcus_sommer — Mon, 20 Feb 2017 11:21:56 GMT

Please provide a csv with a few example-records - both correct ones and the faulty ones and also an example how the faulty records should look like if they are corrected.

Beside them it could be more appropriate to correct these issues within the source respectively to adjust the export into the csv-files. Another approach might be to flag those invalid records and to ignore them per filter or selections within the further evaluations (not each data-quality issue could be solved respectively is it worth to build heavy workarounds and/or to adjust them manually).

- Marcus

Re: Single quote inside columns

sasikanth — Mon, 20 Feb 2017 11:26:31 GMT

HI,

Try below

replace(Text(Description), chr(39), '') as Description

Re: Single quote inside columns

pauldamen — Mon, 20 Feb 2017 13:40:14 GMT

Hi Marcus,

Your answer got me thinking. Also in the meanwhile I found out that there are many scenario's based on the number of quotes in the field but also in the fields after (because if there were quotes there it also changed the length of the string). So I was scripting to many scenario's.

What I decided doing is just to save my CSV as an Excel. This is an extra step but then I can just load the file without having to worry about quotes etc.

Thanks for the help!