Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get Subfield value count

Hi All,,

I have a situation where i need to get the count of field values. I tried substring and subfield both are not working for my situation.I guess using some loops and counter function we can do this. But i cant get the count of states for each field. For customer 111 i want to get count states are 3 similarly customer 222 count of states are 2.

Any help is greatly appreciated.

Original Data table:

Cust   States

111     NY,NJ,CT

222     TX,AL

Ouput table i want is

Cust    States     StatesUpdated

111     NY,NJ,CT     NY

111     NY,NJ,CT     NJ

111     NY,NJ,CT     CT

222     TX,AL          TX

222     TX,AL          AL

1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

Try with subfield function

Load

     cust,

     states,

     SubField(states, ',') AS StatesUpdated

From

Datasource;

Hope it helps

Celambarasan

View solution in original post

4 Replies
alexandros17
Partner - Champion III
Partner - Champion III

you can count number of commas + 1

substringcount (yourField, ',' )

Not applicable
Author

Have you tried substing funciton to count the commas:

substringcount( text , ',')+1 ????

CELAMBARASAN
Partner - Champion
Partner - Champion

Try with subfield function

Load

     cust,

     states,

     SubField(states, ',') AS StatesUpdated

From

Datasource;

Hope it helps

Celambarasan

Not applicable
Author

Awsome, Thankyou i never thought it was that easy.