Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Champion III
Champion III

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


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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

15 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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
Author

Not applicable
Author

I think it has to do with the filter

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

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
Author

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
Partner - Specialist III
Partner - Specialist III

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
Champion III
Champion III

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


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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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