alternative to 'where count()>1'?

[#1]

Hi Everybody,

I have experience using sql.

In sql you can run:

select
name,

salary

from 'x/filepath'

where count(salary)>1;

if there were two employeees named 'Matt Smith' then the resulting data would be

Matt Smith          50k
Matt Smith          47k

is there an equivalent of this in qlikview?

When I put 'where count(salary)>1', it says my execution failed.

Thanks for any help

[MK_QSL]

select
name,

salary

from 'x/filepath'

where count(salary)>1

Group By name;

[#3]

if I am not pulling from a DB but am instead just pulling from an excel sheet is there still a way to apply sql?

[MK_QSL]

doesnt matter you are pulling from a database or from a excel file or as a resident load.... whenever you are aggregating data in script, you have to use GROUP BY with those fields which are part of that load..

[Kushal_Chawda]

Hi,

As far as I know, you cannot use aggregation function in where clause of QlikView script.

You can use the below method for your requirement

Fact:

LOAD * Inline [

Emp, Sal

A,100

B,200

C,300 ];

New:

NoConcatenate

LOAD Emp,

sum(Sal) as SalSum

Resident Fact

Group by Emp;

DROP Table Fact;

Final:

NoConcatenate

LOAD *

Resident New

where SalSum>=200;

DROP Table New;

O/p

Emp Sal

B, 200

C, 300

[ramoncova06]

you are not able to do that as far as I know, though you can do a preceding load

load

Field

value

where ValueTotal < 2;

load

Field,

sum(value) as ValueTotal

from file;

[MK_QSL]

Yes.. You can't use Group By and where together... I didn't notice that while answering... OK... you can use something like below.

Salary:

Load * Where Salary > 125;

Load Name, SUM(Salary) as Salary Group By Name;

Load Name, Salary From FileName;

[qlikviewwizard]

Hi Try this.

Data:

LOAD * where count(salary)>1;

select

name,

sum(salary) as salary

from 'x/filepath'

group by name;

[jagan]

Hi,

Try like this in Qlikview

Data:

LOAD

name,

salary

FROM DataSource;

INNER JOIN(Data)

LOAD

name

WHERE salCount >1;

LOAD

name,

Count(Distinct salary) AS salCount

RESIDENT Data

GROUP By name;

Hope this helps you.

Regards,

jagan.

[sasiparupudi1]

Please close this thread if you got your answer by marking the correct one.