How to get Subfield value count

[#1]

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

[alexandros17]

you can count number of commas + 1

substringcount (yourField, ',' )

[#3]

Have you tried substing funciton to count the commas:

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

[CELAMBARASAN · Accepted solution]

Try with subfield function

Load

     cust,

     states,

     SubField(states, ',') AS StatesUpdated

From

Datasource;

Hope it helps

Celambarasan

[#5]

Awsome, Thankyou i never thought it was that easy.