sql query in Qlikview

[Anonymous]

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

[t_chetirbok]

have you tried to run the query in qlikview?

[bhaskar_sm]

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

[sumanta12]

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;

[Anonymous]

Thanks Bhaskar

[Anonymous]

Thanks Sumanta. ​I got it

[Anonymous]

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 ?

[Anonymous]

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 ?