Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

it only counts the value once

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.

   

NDCDeaClass
00054023525C-II
00054023625C-II
00054023763C-II
00054023863C-II
00054024425C-II
00054026425C-II
00054026525C-II
00054038663C-II
00054040444C-II
00054040450C-II
1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: it only counts the value once

if NDC has multiple values to be counted  then change your expression to below


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

15 Replies

Re: it only counts the value once

Please post a small qlikview document that demonstrates the problem. Without something with actual data it's impossible to tell what (if anything) is wrong.

Not applicable

Re: it only counts the value once

Will this help?  its my SQL

Billing:
LOAD DayName(Billed) as Billed,
    PharmID,
    FacID,
    RxNo,
    DispenseDt,
    RoNo,
    PatID,
    NDC,
    Qty,
    MOP,
    BillAmt,
    BillMethod,
    SentToAccpac,
    OtherPayorAmt,
    DrugLabelName,
    InvCost,
    PayAmt;
SQL SELECT Billed,
    PharmID,
    FacID,
    RxNo,
    DispenseDt,
    RoNo,
    PatID,
    NDC,
    Qty,
    MOP,
    BillAmt,
    BillMethod,
    SentToAccpac,
    OtherPayorAmt,
    DrugLabelName,
    InvCost,
    PayAmt
FROM FwReports.dbo.Billing

Where billed >= '2015-09-01' and FacId <> 'FORMS''MSTR''RETAIL''SAMPLE''TEST''TEST#2''TEST2''TEST3''TRLK''stag';

[BillingArchive]:
LOAD DayName(Billed) as Billed,
    PharmID,
    FacID,
    RxNo,
    DispenseDt,
    RoNo,
    PatID,
    NDC,
    Qty,
    MOP,
    BillAmt,
    BillMethod,
    SentToAccpac,
    OtherPayorAmt,
    DrugLabelName,
    InvCost,
    PayAmt;
SQL SELECT Billed,
    PharmID,
    FacID,
    RxNo,
    DispenseDt,
    RoNo,
    PatID,
    NDC,
    Qty,
    MOP,
    BillAmt,
    BillMethod,
    SentToAccpac,
    OtherPayorAmt,
    DrugLabelName,
    InvCost,
    PayAmt

 
FROM "FwReports"."dbo"."BillingArchive"
Where billed >= '2015-09-01' and FacId <> 'FORMS''MSTR''RETAIL''SAMPLE''TEST''TEST#2''TEST2''TEST3''TRLK''stag';

DEAclass:
LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD NDC,
   DeaClass,
    RxOtcInd,
    BrandNameCd;
SQL SELECT NDC,
    DeaClass,
    RxOtcInd,
    BrandNameCd
FROM FwReports.dbo.KeyIdentifiers;

Not applicable

Re: it only counts the value once

Not applicable

Re: it only counts the value once

I think it has to do with the filter

phaneendra_kunc
Valued Contributor III

Re: it only counts the value once

Baiscally it is because of in DEA Class table you have unique records and in BillingArchive table you have dulpicates due to date.

So when you count NDC it will include those duplicates as NDC is a key between these two tables (meaning combination of both tables).

And when you do a count on DEAclass "CII" you will get a uinique count as you are referring to only DEAclass table and that has unique records.

A sample app that i have created using your data in attached.

Added RowNo() to see where the duplicates are coming from. and If you want a true NDC count i would suggest to do a distinct or create a new aliased field on DEAClass side soem thing like "NDC as DEANDC" in script.

Not applicable

Re: it only counts the value once

does not work, did I miss something?

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

[BillingArchive]:
Load
    Billed,
    NDC,
    NDC as billingNDC,
    Rowno () as billingRow
SQL SELECT Billed,
     NDC,
     NDC
FROM "FwReports"."dbo"."BillingArchive"
Where billed >= '2015-11-01' and billed <= '2015-11-30';

DEAclass:
LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD NDC,
    DeaClass,
    RxOtcInd,
    BrandNameCd,
    NDC as DEANDC,
    RowNo () as DeaRow
SQL SELECT NDC,
    DeaClass,
    RxOtcInd,
    BrandNameCd
FROM FwReports.dbo.KeyIdentifiers;

phaneendra_kunc
Valued Contributor III

Re: it only counts the value once

Yes a Semi Colon at the end of Load statement. Add semi colon at the end of below in you code.

Rowno () as billingRow;


RowNo () as DeaRow;

vinieme12
Esteemed Contributor II

Re: it only counts the value once

if NDC has multiple values to be counted  then change your expression to below


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

Not applicable

Re: it only counts the value once

There are actually 3,984 C-II  so missing 197