MSSQL Server Query in QlikView

[Anonymous]

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

[Anil_Babu_Samineni]

You have to use group by clause for aggregate functions

[its_anandrjs]

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;

[Anonymous]

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.

[Anonymous]

Thanks Anand, let me try it out and advise accordingly

[maxgro]

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?

[Anonymous]

Thanks Massimo, doesn't the 'count' statement require a Group By clause?

[maxgro]

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

[Anil_Babu_Samineni]

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];

[Anonymous]

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.