it only counts the value once

[#1]

I have an app that part of the data comes from excel files and part is a direct connect.

I used the expression below on a total direct connect to count all like data in the field and it worked but when I us the same expression in my new file it only counts one DeaClass C-II per NDC.

count({<DeaClass={'C-II'}>}DeaClass)

The data below is after I removed all duplicates from the excel data file.

I am converting file from excel to direct connect desktop to enterprise.

NDC	DeaClass
00054023525	C-II
00054023625	C-II
00054023763	C-II
00054023863	C-II
00054024425	C-II
00054026425	C-II
00054026525	C-II
00054038663	C-II
00054040444	C-II
00054040450	C-II

[phaneendra_kunc]

3,984 C-II in BillingArchive? as far as i see for C-ll i am getting a match of 3,786 from Billing Archive.

[phaneendra_kunc]

Not sure how this would count the duplicates. you will be targeting unique records as soon as you add that set anlaysis.

With duplicate for C-II is 12K, and unique values are 8k.

[vinieme12]

The OP actually wanted to count NDC , and if you want to count NDC then count NDC not any other field.

the same expression in my new file it only counts one DeaClass C-II per NDC.

I can't open the qvf; but i don't think QV will calculate unique values until you add the Distinct keyword.

[Gysbert_Wassenaar]

NDC is a key field. Do NOT use count on a key field unless it's a count distinct. Use another field from the table of which you want the count. Read this blog post for the details: Count or Count distinct?

[Gysbert_Wassenaar]

You load the field NDC twice in the SQL Select statement. You need to either remove one of those two lines or alias the field so it gets another name. Tables need unique field names.

BillingArchive]:

Load

    Billed,

    NDC,

    NDC as billingNDC,

    Rowno () as billingRow

SQL SELECT Billed,

// remove this line     NDC,

     NDC

FROM "FwReports"."dbo"."BillingArchive"

Where billed >= '2015-11-01' and billed <= '2015-11-30';

[phaneendra_kunc]

Thanks Gysbert.

Vineeth hope this helps to understand on why we don't usually do count on key fields.