Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following SQL Query which i am executing in MSSQL Server Management Studio:
SELECT COUNT(DISTINCT [Script Number]) AS RxCount
FROM tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2017-09-01' and [Date Dispensed] <= '2017-09-30'
How can the above SQL Query be executed in QlikView load script statement?
Regards.
Chris
You have to use group by clause for aggregate functions
Try with the below statement
LOAD
COUNT(DISTINCT [Script Number]) AS RxCount
WHERE [Date Dispensed] >= '2017-09-01' and [Date Dispensed] <= '2017-09-30';
sql select
FROM tblDatawarehouseConsolidatedCleanStage;
Many thanks Anil
If i use a group by clause, the value of "Rxcount" i get in QlikView is overstated compared to the one i get directly in MSSQL Server Management Studio.
Thanks Anand, let me try it out and advise accordingly
Is there a specific reason you want to extract all the records from the Sql Server table tblDatawarehouseConsolidatedCleanStage,
transfer all the records from Sql Server to Qlik and then calculate in Qlik a single value, the count distinct ....?
Why not just a
SQL SELECT COUNT(DISTINCT [Script Number]) AS RxCount
FROM tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2017-09-01' and [Date Dispensed] <= '2017-09-30'
in Qlik?
Thanks Massimo, doesn't the 'count' statement require a Group By clause?
no, it doesn't
you can use the group by, it depends on what you want to achieve
example:
input data is on the left,
the result of the query is on the right
- right top: count distinct
- right bottom: count distinct with a group by dim
Group by is mandatory where aggregations line sum, count ... to persists you can try with this
Load:
SELECT [Script Number], [Date Dispersed]
FROM tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2017-09-01' and [Date Dispensed] <='2017-09-30';
Load [Date Dispensed], count([Script Number] as RxCount Resident Load
Group by [Date Dispensed];
The reason why i am calculating RxCount at load step in the front end is that i need to use it in QV complex scripts like:
Num(Sum({<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]))+1,-12)) <=$(=max([Date Dispensed]))"}>}RxCount), '#,##0')
Regards.