topic Re: Count in one table in QlikView

Count in one table

Mon, 15 Sep 2014 10:06:37 GMT

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!

Re: Count in one table

ogster1974 — Mon, 15 Sep 2014 10:12:54 GMT

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

Re: Count in one table

Mon, 15 Sep 2014 10:13:10 GMT

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

Re: Count in one table

MarcoWedel — Mon, 15 Sep 2014 10:14:20 GMT

count on another field that exists only in one table

Re: Count in one table

MayilVahanan — Mon, 15 Sep 2014 10:32:21 GMT

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.

Simply use, = Count(distinct StoreID)

Re: Count in one table

Mon, 15 Sep 2014 14:17:30 GMT

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

Re: Count in one table

ogster1974 — Tue, 16 Sep 2014 14:40:06 GMT

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.