Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
select
name,
salary
from 'x/filepath'
where count(salary)>1
Group By name;
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?
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..
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
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;
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;
Hi Try this.
Data:
LOAD * where count(salary)>1;
select
name,
sum(salary) as salary
from 'x/filepath'
group by name;
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.
Please close this thread if you got your answer by marking the correct one.