Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
count on another field that exists only in one table
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)
hi
even if i flag it on one table ,it still brings multiple due to the fact that the other table has more records
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.