Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Rb181
Contributor II
Contributor II

Field Categories are not Delimited - should be single entities not multiple

I've just made my first app based on the data uploaded - its a csv using comma as the delimiter. 

When I visualise the data however, and try to filter it, it does not distinguish between the 'categories' and reads all of the possible options whilst disregarding the commas - they should all be single entities: 

Screenshot 2024-02-05 at 09.51.56.png

 

Each of the above should not be mutiple comma separated options, rather they should all be separated and single entities:

  • Chronic Pain 
  • Neurological 
  • Muscuoloskeletal....
  • ....etc

This is how the data is formatted, and it is set as delimited and comma as delimiter

Screenshot 2024-02-05 at 09.52.28.png

Apologies is this is something basic I am missing, but thought I had configured the data load properly. 

Thanks

Labels (1)
1 Solution

Accepted Solutions
qv_testing
Specialist II
Specialist II

I feel spaces are not removed, can you try with TRIM(Field).

LOAD FieldName,
           Trim(subfield(FieldName, ',')) as NewField
resident tablename;

View solution in original post

8 Replies
marcus_sommer

It seems that these field-values have a quoting - probably with double-quotes - which prevents that the field-delimiter is applied on these values. If so - it's good and shouldn't be changed. To split this field just add an extra step, like:

subfield(MyField, ',') as MySubField

to the load.

qv_testing
Specialist II
Specialist II

If you have huge data, my suggestion is to create it as a separate table instead of the same load.

LOAD FieldName,
           subfield(FieldName, ',') as NewField
resident tablename;

Rb181
Contributor II
Contributor II
Author

Thanks for the help @marcus_sommer and @qv_testing - I have 30 columns, and many of them have this issue - they all have multiple strings in them which need to be broken up. So yes perhaps I should create them all as different tables.

Rb181
Contributor II
Contributor II
Author

Seems to have got me halfway there (due to my limited understanding) - oddly, some categories have duplicated themselves into 2 distinct groups for no apparent reason - I've formatted the data exactly the same (no difference in cases, spaces etc), but for some reason it is splitting it randomly: 

Screenshot 2024-02-05 at 16.19.15.png

Ever come across this?

Thanks

qv_testing
Specialist II
Specialist II

I feel spaces are not removed, can you try with TRIM(Field).

LOAD FieldName,
           Trim(subfield(FieldName, ',')) as NewField
resident tablename;

marcus_sommer

It just means that the values aren't the same else they differ in anything. You could put them into a table-box and export them to EXCEL and/or a csv (opened with an editor like NOTEPAD++) to look for the real content. Quite probably are there any kind of spaces and/or special chars included.- which could then be removed with keepchar() or purgechar().

Another reason for such behaviour are dual() values - means a numeric value has also a string-representation and in this case the numeric values will determine the view.

Rb181
Contributor II
Contributor II
Author

Thanks for the insights. I am using notion to assign tags to each field, so they should all be identical. I then export to .csv. 

Perhaps it is the dual values....

Rb181
Contributor II
Contributor II
Author

Thank you this seems to have done the trick!