Problems using SubField in an table expression in Expression Editor

[Anonymous]

Hi Community

I've recently started using functions in table expressions in Qlik Sense and have come across a problem when using SubField.

When I use SubField in the Expression Editor in a basic table it almost works but falls over.

In one Measure called Delivery Locations I have a string of service locations separated by commas with a space afterwards like the following: "JOONDALUP, ROCKINGHAM, ROCKINGHAM DC, BUNBURY, KALGOORLIE, GERALDTON"

I am trying to use subfield to get each location to appear on a separate row in the table.

In the Expression Editor I have =subfield([Delivery Locations],',')

Using this expressing, for the first record with multiple locations, as above, "JOONDALUP, ROCKINGHAM, ROCKINGHAM DC, BUNBURY, KALGOORLIE, GERALDTON" it produces the following results in the Individual Locations column - giving only three locations of the six in the string.

For other Activities it gives more locations than in the string, it duplicates locations and also gives locations which are not even present in the string like the following:

Can anyone give me a hand to correct this issue or to find another solution?

Thanks

Paul

[ogautier62]

Hi,

you could create rows in script :

location:

load *, SubField(location,',',iterno()) as IndividualLocation resident yourdatafile

while iterno()<=SubStringCount(location,',')+1;

so you have a new table with individual rows :

or in front end :

add valueloop(1, 10)

if 10 is max of cities in location

and as expression :

=subfield(location,',',valueloop(1,10))

regards

[fah18]

Hi, 

I am facing the same issue but with a word cloud and a table that returns the string of the word. Did you solve this issue? Can you guide me a bit?