Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
if NDC has multiple values to be counted then change your expression to below
count({<DeaClass={'C-II'}>} NDC)
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.
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;
I think it has to do with the filter
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.
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;
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;
if NDC has multiple values to be counted then change your expression to below
count({<DeaClass={'C-II'}>} NDC)
There are actually 3,984 C-II so missing 197