Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
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!