topic Re: sql query in Qlikview in QlikView

sql query in Qlikview

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

hi frds...

i want show same result in qlikview? mention below sql query what can i do?

whene i Execute below qury in oracle , got result so i want same result in qlikview please ans

select job_id, avg(salary) from employees group by job_id having avg(salary)>(select avg(salary)from employees group by job_id

HAVING job_id='AC_MGR') ORDER BY JOB_ID;

output=

Job_id avg(salary)

AD_PRES 24000

AD_VP 17000

MK_MAN 13000

SA_MAN 12200

Re: sql query in Qlikview

t_chetirbok — Wed, 02 May 2018 19:46:53 GMT

have you tried to run the query in qlikview?

Re: sql query in Qlikview

bhaskar_sm — Wed, 02 May 2018 20:07:58 GMT

Hi,

You just need to put Sql query in Qlikview- Script Editor-Like Below.

Query: // Give TableName

select job_id, avg( salary) from employees group by job_id having avg(salary) > (select avg(salary) from employees group by job_id

HAVING job_id='AC_MGR' ) ORDER BY JOB_ID;

please not that you need to configure the source data-Oracle connection( basically setting up ODBC driver) with Qlikview first before executing it.

Regards,

Bhasker Kumar

Re: sql query in Qlikview

sumanta12 — Thu, 03 May 2018 12:40:28 GMT

Hi Santoshkumar,

You can try like this:

EMPLOYEES:

LOAD * INLINE [

JOB_ID,SAL

AD_PRES,22000

AD_VP,15000

MK_MAN,14000

SA_MAN,11200

AD_PRES,24000

MK_MAN,18000

SA_MAN,11222

AD_VP,11000

AC_MGR,13500

AC_MGR,12000

AC_MGR,16000

];

NOCONCATENATE

EMPLOYEES2:

LOAD

JOB_ID,

AVG(SAL) AS AVG_SAL

RESIDENT EMPLOYEES WHERE JOB_ID<>'AC_MGR'

GROUP BY JOB_ID;

CONCATENATE

LOAD

JOB_ID,

AVG(SAL) AS AC_MGR_AVG_SAL

RESIDENT EMPLOYEES WHERE JOB_ID='AC_MGR'

GROUP BY JOB_ID;

LET VMGR=PEEK('AC_MGR_AVG_SAL',NOOFROWS('EMPLOYEES2')-1,'EMPLOYEES2');

NOCONCATENATE

EMPLOYEES3:

LOAD

JOB_ID,

AVG_SAL

RESIDENT EMPLOYEES2 WHERE AVG_SAL>$(VMGR);

DROP TABLES EMPLOYEES,EMPLOYEES2;

Re: sql query in Qlikview

Anonymous — Sat, 12 May 2018 06:20:31 GMT

Thanks Bhaskar

Re: sql query in Qlikview

Anonymous — Sat, 12 May 2018 06:23:57 GMT

Thanks Sumanta. I got it

Re: sql query in Qlikview

Anonymous — Sat, 12 May 2018 06:54:56 GMT

t.chetirbok ..... Thanks

Actually i loaded Oracle 10g HR Schema in Qlikview . I have four tables Employees,Job_History,Departments,Location

Data Model is OK or not ???

My question is that

just i want to show 4 record in pivot table

have you got my question ?

Re: sql query in Qlikview

Anonymous — Sat, 12 May 2018 06:56:48 GMT

Thanks Bhasker

Actually i loaded Oracle 10g HR Schema in Qlikview . I have four tables Employees,Job_History,Departments,Location

Data Model is OK or not ???

My question is that

just i want to show 4 record in pivot table

have you got my question ?