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

[maxgro]

Some options:

1) load the fields [Script Number] and [Date Dispensed] without group by and calculate in the chart

SQL

SELECT [Script Number] AS RxCount, [Date Dispensed]

FROM tblDatawarehouseConsolidatedCleanStage

WHERE [Date Dispensed] >= '2017-09-01' and [Date Dispensed] <= '2017-09-30';

2) group by date in the sql section of the Qlik load

Load *;

SQL

SELECT

    COUNT(DISTINCT [Script Number]) AS RxCount,

    [Date Dispensed]

FROM tblDatawarehouseConsolidatedCleanStage

WHERE [Date Dispensed] >= '2017-09-01' and [Date Dispensed] <= '2017-09-30'

group by [Date Dispensed];

3) group by date in the Qlik section of the Qlik preceding load

Load

    [Date Dispensed],

    count(distinct [Script Number]) as RxCount

Group by [Date Dispensed];

SELECT

    [Script Number],

    [Date Dispensed]

FROM tblDatawarehouseConsolidatedCleanStage

WHERE [Date Dispensed] >= '2017-09-01' and [Date Dispensed] <= '2017-09-30';

[Anonymous]

Thanks Massimo.  Part of my model is as follows:

The total number of Prescriptions dispensed (Rx) in my data is 5 151 692 which i got through a select distinct [Script Number] in MSSQL Server Management studio.

I did a normal load and added a variable Rx calculated as follows:

=Count(DISTINCT [Script Number])

I then want to get the prescriptions dispensed for month to date through set analysis as follows:

=Num(Sum({$<$(vSetMTD)>}Rx), '#,##0')

This figure gives the value of 5 151 692 (the total number of prescriptions) meaning that it is not responding to set analysis or selections.

The percentage is the change over the last period, is zero meaning a constant figure of 5 151 692 is being returned.

A table which summarises the prescription count (script count) per retail outlet also gives total values as follows:

However in the same model i have the following:

=Num(Sum({$<$(vSetMTD)>}Amount), '$##,##0.00')

The above gives the right value of total value of prescriptions dispensed month to date and the "Amount" is loaded in the front end which is:

What could be taking place?

Regards.