topic Preceding Distinct Load and Count in QlikView

Preceding Distinct Load and Count

Anonymous — Wed, 18 Nov 2015 19:48:30 GMT

I am trying to do a distinct load with a count in a preceding load like as follows:

[Script Count]:

Load ScriptNumber,

Count (ScriptNumber) as RX;

SQL SELECT DISTINCT ScriptNumber

FROM Mysever.dbo.MyDatabase;

This gives an invalid expression error. Kindly assist

Re: Preceding Distinct Load and Count

sunny_talwar — Wed, 18 Nov 2015 19:50:03 GMT

You would need a Group By Statement any time you do Aggregation within a load having other fields outside the aggregation.

Try this:

[Script Count]:

Load ScriptNumber,

Count (ScriptNumber) as RX

Group By ScriptNumber;

SQL SELECT DISTINCT ScriptNumber

FROM Mysever.dbo.MyDatabase;

or may be this if you don't need overall count (rather than count per ScriptNumber)

[Script Count]:

Load Count (ScriptNumber) as RX;

SQL SELECT DISTINCT ScriptNumber

FROM Mysever.dbo.MyDatabase;

Re: Preceding Distinct Load and Count

Anonymous — Wed, 18 Nov 2015 19:56:16 GMT

Thanks Suny let me try it out and will dvise accordingly

Re: Preceding Distinct Load and Count

maxgro — Wed, 18 Nov 2015 20:02:31 GMT

why not at the db side?

SELECT COUNT(DISTINCT ScriptNumber) AS RX

FROM Mysever.dbo.MyDatabase;

but there is a think I don't understand

If you do a distinct ScriptNumber on the db,

you can only get distinct values

...

1000

and the count is always 1

Re: Preceding Distinct Load and Count

ElizaF — Wed, 18 Nov 2015 20:28:53 GMT

Try this:

[Script Count]:

Load

ScriptNumber,

RX;

SQL SELECT DISTINCT ScriptNumber, Count (ScriptNumber) as RX

FROM Mysever.dbo.MyDatabase

Group by ScriptNumber ;

Re: Preceding Distinct Load and Count

Anonymous — Wed, 18 Nov 2015 21:08:08 GMT

Hi Sunny

Many thanks, the first option is what i was looking for. Can you explain a little bit more what is exactly taking place.

Re: Preceding Distinct Load and Count

Anonymous — Wed, 18 Nov 2015 21:09:57 GMT

Many thanks, as you said, if i do it at the data base side i will lose some valuable data.

Re: Preceding Distinct Load and Count

Anonymous — Wed, 18 Nov 2015 21:11:37 GMT

Thanks Eliza

I will also try this out