Subfield Rows count

[vardhancse]

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

[kaushiknsolanki]

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

[vardhancse]

Hi Kaushik,

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

[vinieme12]

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

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

[vinieme12]

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;

[vardhancse]

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.

[sunny_talwar]

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)