topic How to get Subfield value count in QlikView

How to get Subfield value count

Wed, 19 Jun 2013 15:11:41 GMT

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

Re: How to get Subfield value count

alexandros17 — Wed, 19 Jun 2013 15:19:51 GMT

you can count number of commas + 1

substringcount (yourField, ',' )

Re: How to get Subfield value count

Wed, 19 Jun 2013 15:20:13 GMT

Have you tried substing funciton to count the commas:

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

Re: How to get Subfield value count

CELAMBARASAN — Wed, 19 Jun 2013 15:37:55 GMT

Try with subfield function

Load

cust,

states,

SubField(states, ',') AS StatesUpdated

From

Datasource;

Hope it helps

Celambarasan

Re: How to get Subfield value count

Wed, 19 Jun 2013 15:59:38 GMT

Awsome, Thankyou i never thought it was that easy.