topic Re: Subfield Rows count in QlikView

Subfield Rows count

vardhancse — Wed, 24 Jan 2018 08:06:48 GMT

Hi,

I have one table similar to below;

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

Using Subfield function, resolved my issue

Fact:

Load

cust,

states,

SubField(states, ',') AS StatesUpdated

From

Datasource;

Now facing issue while taking count of records rfom fact table;

Ideally records count is "2", but post Subfield now the count is "5". I can use distinct but issue is in my data set Cust field is not unique

can any one help me out to resolve the same

Re: Subfield Rows count

kaushiknsolanki — Wed, 24 Jan 2018 08:09:43 GMT

Hi,

If you want to have the distinct values, you can do one thing, keep 2 tables.

1. with the original data (Cust,States)

2. with the subfield data (States ,StatesUpdated)

Regards,

Kaushik Solanki

Re: Subfield Rows count

vardhancse — Wed, 24 Jan 2018 08:14:08 GMT

Hi Kaushik,

Thank you, but maintaining 2 separate tables may lead to some issues

Re: Subfield Rows count

vinieme12 — Wed, 24 Jan 2018 09:25:05 GMT

Load with Subfield only and in chart add Dimensions Cust and States

and Expression : Concat(TOTAL <Cust> DISTINCT States,',')

Re: Subfield Rows count

vinieme12 — Wed, 24 Jan 2018 09:28:24 GMT

Ideally records count is "2", but post Subfield now the count is "5". I can use distinct but issue is in my data set Cust field is not unique

So how do you want the Totaling to be done, if it should be based on Cust and Concatenated States combination the use Autonumber() while loading

Fact:

Load

cust,

states,

Autonumber(cust&states) as UniqueGroupID

SubField(states, ',') AS StatesUpdated

From

Datasource;

Re: Subfield Rows count

vardhancse — Wed, 24 Jan 2018 09:40:56 GMT

Hi,

Its not for one charts, I have many text objects and other objects.

I want the count of Cust and I want ot match the records count with respect to source.

Re: Subfield Rows count

sunny_talwar — Wed, 24 Jan 2018 13:08:04 GMT

One option is to use RecNo() to create a unique count for each row from the original table and then DISTINCT count of this new field should give you the original count

Table:

LOAD RecNo() as OriginalRowCount,

SubField(States, ',') AS StatesUpdated;

LOAD * INLINE [

Cust, States

111, "NY, NJ, CT"

222, "TX, AL"

];

=Count(DISTINCT OriginalRowCount)