topic Re: MSSQL Server Query in QlikView in QlikView

MSSQL Server Query in QlikView

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: MSSQL Server Query in QlikView

Anil_Babu_Samineni — Sat, 21 Oct 2017 13:06:40 GMT

You have to use group by clause for aggregate functions

Re: MSSQL Server Query in QlikView

its_anandrjs — Sat, 21 Oct 2017 13:29:53 GMT

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;

Re: MSSQL Server Query in QlikView

Anonymous — Sat, 21 Oct 2017 13:30:00 GMT

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.

Re: MSSQL Server Query in QlikView

Anonymous — Sat, 21 Oct 2017 13:30:58 GMT

Thanks Anand, let me try it out and advise accordingly

Re: MSSQL Server Query in QlikView

maxgro — Sat, 21 Oct 2017 13:49:21 GMT

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?

Re: MSSQL Server Query in QlikView

Anonymous — Sat, 21 Oct 2017 14:05:31 GMT

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

Re: MSSQL Server Query in QlikView

maxgro — Sat, 21 Oct 2017 14:26:59 GMT

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

Re: MSSQL Server Query in QlikView

Anil_Babu_Samineni — Sat, 21 Oct 2017 14:41:01 GMT

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

Re: MSSQL Server Query in QlikView

Anonymous — Sat, 21 Oct 2017 15:24:05 GMT

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.

Re: MSSQL Server Query in QlikView

maxgro — Sat, 21 Oct 2017 17:24:00 GMT

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

Re: MSSQL Server Query in QlikView

Anonymous — Sun, 22 Oct 2017 08:57:34 GMT

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.