Count in one table

[#1]

Hi All

I have two table linking on StoreIDs and want to count the number of store in Table B e.g Count(distinct StoreID) but it duplicates counts due to the fact that Table A has more than 1 records for a specific store,how can i go about counting stores only in table A..

Thanks in advance!

[ogster1974]

Load the 2 tables and include a column that identifies 'tableA' and 'tableB' you will then be able to use set analysis to count on the stores filtered by either tablea or tableb.

e,g

COUNT({$ <table={'tableB'}>} StoreId)

Hope this helps

[#3]

Hi,

You could use a Flag field in the table you want to count, and then use Set Analysis.

Something like:

Count({<Flag=1>}distinct StoreID)

Greetings,

Angel

[MarcoWedel]

count on another field that exists only in one table

[MayilVahanan]

Hi

Try like this

Load *, 1 as flag Inline
[
StoreID, StoreName
1, XXX
2, YYY
];

TableA:
LOAD * Inline
[
StoreID, Values
1,1000
1,2000
1,3000
2,2000
];

Expression:

= Sum(flag)  //gives 2 as result.

or

Simply use, = Count(distinct StoreID)

[#6]

hi

even if i flag it on one table ,it still brings multiple due to the fact that the other table has more records

[ogster1974]

Rather than load separate sources concatenate the into 1 source.  In my example sales and stock I have concatenated in the data but can be done in the load to.  Identify the source (TableA/TableB) and the StoreId the rest of the columns are source specific.  Note tableB has duplicate StoreIds.

Its then easy to use the Source as a dimension in charts/table and COUNT(DISTINCT [%StoreKey]) or if you only need the count from one source use set analysis COUNT(DISTINCT {$ <Source={'TableA'}>} [%StoreKey])

Hope this has helped.