Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

MSSQL Server Query in QlikView

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


11 Replies
Anil_Babu_Samineni

You have to use group by clause for aggregate functions

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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
Not applicable
Author

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
Not applicable
Author

Thanks Anand, let me try it out and advise accordingly

maxgro
MVP
MVP

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
Not applicable
Author

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

maxgro
MVP
MVP

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

1.png

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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.