Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Problems using SubField in an table expression in Expression Editor

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

1 Reply
Specialist II
Specialist II

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