Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

sql query in Qlikview

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

7 Replies
Highlighted
Specialist
Specialist

have you tried to run the query in qlikview?

Highlighted
Partner
Partner

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


Highlighted
Creator II
Creator II

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;

Highlighted
Contributor II
Contributor II

Thanks Bhaskar

Highlighted
Contributor II
Contributor II

Thanks Sumanta. ​I got it

Highlighted
Contributor II
Contributor II

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 ???

hr Schema.PNG

My question is that

sql.PNG

just i want to show 4 record in pivot table

have you got my question ?

Highlighted
Contributor II
Contributor II

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 ???

hr Schema.PNG

My question is that

sql.PNG

just i want to show 4 record in pivot table

have you got my question ?