topic Re: Sql query to Qlikview Script.. in QlikView

Sql query to Qlikview Script..

Wed, 24 Aug 2016 08:55:51 GMT

Hi Friends,

I have below sample SQL Query which I need to convert into equivalent Qlikview (AQL) Script. Please guide me accordingly.

SELECT student_clasification_SOURCE student_source

,ei.e_item_id

,EI.E_ITEM_DATE

,EI.E_TYPE Expnd_type

,(SELECT e_category

FROM table12

WHERE ex_type = ei.ex_type)

EXPND_CATEGORY

,DECODE (EI.SLI_FUNCTION,

'USG',

'Usages',

'PJ',

'Miscellaneous ') Type_Class

,p.segment1 project_number

,x.E_GROUP Expenditure_batch

,x.CR_DATE Expnd_batch_creation_date

,(SELECT p.full_name

FROM Ptable11 p

WHERE p.student_id = x.incurred_by_student_id

AND EI.Actual_date BETWEEN p.effective_start_date

AND p.effective_end_date)

EMPLOYEE_NAME

,(SELECT DECODE (p.current_employee_flag,

'Y', p.student_number,

NVL (p.npw_number, p.student_number))

FROM table11 p

WHERE p.student_id = x.incurred_by_student_id

AND ei.Actual_date BETWEEN p.effective_start_date

AND p.effective_end_date)

student_NUMBER

,x.incurred_by_student_id Student_id

FROM Table1 P,

Table2 T,

Table3 I,

TAble4 X,

Table5 PT,

Table6 TR,

TAble7 O1,

TAble8 J

WHERE T.field7 = P.field7

AND I.field6_ID = P.field6_ID

AND P.filed4type = PT.field4type

AND P.field3_ID = PT.field3_ID

AND I.field2_ID = T.field2_ID

AND I.field1_ID = X.field1_ID

AND NVL (I.OVERRIDE_TO_ORGANIZATION_ID,

X.IN_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID

AND I.sub_ID = J.sub_ID(+)

AND I.classification_SOURCE = TR.classification_SOURCE(+)

AND tr.student_clasification_SOURCE is null

--order by x.in_by_Student_id ;

Regards,

Niki

Re: Sql query to Qlikview Script..

jpenuliar — Wed, 24 Aug 2016 09:25:43 GMT

Hi Nikitha,

you can load data off database using SQL:

SQL ‒ QlikView

Re: Sql query to Qlikview Script..

Wed, 24 Aug 2016 09:50:00 GMT

Hi Jonathan,

Thank you for the reply...

But my requirement is to convert the SQL query mentioned above to equivalent Qlikview...

SO it would be helpful if you guide in that manner...I am not well versed with SQL query..so stuck with it...

Regards,

Niki

Re: Sql query to Qlikview Script..

agomes1971 — Wed, 24 Aug 2016 10:11:20 GMT

Hi,

a advice create a table with data an then import it to QlikView it will work faster.

Lije this:

create view put_a_name as

SELECT student_clasification_SOURCE student_source

,ei.e_item_id

,EI.E_ITEM_DATE

,EI.E_TYPE Expnd_type

,(SELECT e_category

FROM table12

WHERE ex_type = ei.ex_type)

EXPND_CATEGORY

,DECODE (EI.SLI_FUNCTION,

'USG',

'Usages',

'PJ',

'Miscellaneous ') Type_Class

,p.segment1 project_number

,x.E_GROUP Expenditure_batch

,x.CR_DATE Expnd_batch_creation_date

,(SELECT p.full_name

FROM Ptable11 p

WHERE p.student_id = x.incurred_by_student_id

AND EI.Actual_date BETWEEN p.effective_start_date

AND p.effective_end_date)

EMPLOYEE_NAME

,(SELECT DECODE (p.current_employee_flag,

'Y', p.student_number,

NVL (p.npw_number, p.student_number))

FROM table11 p

WHERE p.student_id = x.incurred_by_student_id

AND ei.Actual_date BETWEEN p.effective_start_date

AND p.effective_end_date)

student_NUMBER

,x.incurred_by_student_id Student_id

FROM Table1 P,

Table2 T,

Table3 I,

TAble4 X,

Table5 PT,

Table6 TR,

TAble7 O1,

TAble8 J

WHERE T.field7 = P.field7

AND I.field6_ID = P.field6_ID

AND P.filed4type = PT.field4type

AND P.field3_ID = PT.field3_ID

AND I.field2_ID = T.field2_ID

AND I.field1_ID = X.field1_ID

AND NVL (I.OVERRIDE_TO_ORGANIZATION_ID,

X.IN_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID

AND I.sub_ID = J.sub_ID(+)

AND I.classification_SOURCE = TR.classification_SOURCE(+)

AND tr.student_clasification_SOURCE is null

--order by x.in_by_Student_id ;

and then do:

Select * into new_table from put_a_name

HTH

André Gomes

Re: Sql query to Qlikview Script..

Wed, 24 Aug 2016 10:15:45 GMT

Hi André Gomes ,

Thanks for the advice..

But I need equivalent Qlikview Script for Each Sub Queries and Queries of SQl as mentioned above..

So please help me in doing that.

Regards,

Niki

Re: Sql query to Qlikview Script..

manideep78 — Wed, 24 Aug 2016 10:16:14 GMT

Hi Niki,

Create a view using the above query and then you pull that query to qlikview.

EG:

CREATE VIEW <View_Name>

SELECT student_clasification_SOURCE student_source

,ei.e_item_id

,EI.E_ITEM_DATE

,EI.E_TYPE Expnd_type

,(SELECT e_category

FROM table12

WHERE ex_type = ei.ex_type)

EXPND_CATEGORY

,DECODE (EI.SLI_FUNCTION,

'USG',

'Usages',

'PJ',

'Miscellaneous ') Type_Class

,p.segment1 project_number

,x.E_GROUP Expenditure_batch

,x.CR_DATE Expnd_batch_creation_date

,(SELECT p.full_name

FROM Ptable11 p

WHERE p.student_id = x.incurred_by_student_id

AND EI.Actual_date BETWEEN p.effective_start_date

AND p.effective_end_date)

EMPLOYEE_NAME

,(SELECT DECODE (p.current_employee_flag,

'Y', p.student_number,

NVL (p.npw_number, p.student_number))

FROM table11 p

WHERE p.student_id = x.incurred_by_student_id

AND ei.Actual_date BETWEEN p.effective_start_date

AND p.effective_end_date)

student_NUMBER

,x.incurred_by_student_id Student_id

FROM Table1 P,

Table2 T,

Table3 I,

TAble4 X,

Table5 PT,

Table6 TR,

TAble7 O1,

TAble8 J

WHERE T.field7 = P.field7

AND I.field6_ID = P.field6_ID

AND P.filed4type = PT.field4type

AND P.field3_ID = PT.field3_ID

AND I.field2_ID = T.field2_ID

AND I.field1_ID = X.field1_ID

AND NVL (I.OVERRIDE_TO_ORGANIZATION_ID,

X.IN_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID

AND I.sub_ID = J.sub_ID(+)

AND I.classification_SOURCE = TR.classification_SOURCE(+)

AND tr.student_clasification_SOURCE is null

--order by x.in_by_Student_id ;

***********************************

In qlikview : LOAD *;

SQL Select * from <View_Name>

Regards,

Manideep

Re: Sql query to Qlikview Script..

Wed, 24 Aug 2016 10:24:23 GMT

Hi Manideep,

Thanks for the reply...

I have done that.. I have already have the Finally data.

But I need the qlikview equivalent code Snippet of the above SQL query

Regards,

Niki

Re: Sql query to Qlikview Script..

agomes1971 — Wed, 24 Aug 2016 10:25:17 GMT

Hi,

in this article i have there the answer for you...

SQL Queries with Qlikview

Please proceed as acording to it.

HTH

André Gomes

Re: Sql query to Qlikview Script..

jpenuliar — Wed, 24 Aug 2016 10:42:49 GMT

Hi Nikitha,

If i understand correctlly what is your objective:

1. You need to Load individual Tables (Table1 to Table8) in Qlikview

2. Use the loaded Tables and JOIN (as the SQL dictates)

3. USE MAPPING LOAD to return Type_Class (this is ins SQL subquery)

4. there's some other stuff I probably am missing.

So all in all, your requirement is to come up with a data model based on the given SQL statement.

Re: Sql query to Qlikview Script..

Wed, 24 Aug 2016 10:51:15 GMT

Hi Jonathan,

You are correct. My objective is the same...

Thank you....

It would be more helpful...if it is more elaborate.

Regards,

Niki

Re: Sql query to Qlikview Script..

agomes1971 — Wed, 24 Aug 2016 16:29:22 GMT

Hi,

Nikitha,

you have to follow the instructions from Jonathan.

It's hard to help you without acess to data.

I'll try to do the scripting anyway.

André Gomes

Re: Sql query to Qlikview Script..

Anil_Babu_Samineni — Wed, 24 Aug 2016 17:14:02 GMT

You are using

SELECT student_clasification_SOURCE student_source

,ei.e_item_id

,EI.E_ITEM_DATE

,EI.E_TYPE Expnd_type

,(SELECT e_category

FROM table12

In that, Where do you use this ei, EI

FROM Table1 P,

Table2 T,

Table3 I,

TAble4 X,

Table5 PT,

Table6 TR,

TAble7 O1,

TAble8 J

Re: Sql query to Qlikview Script..

ramkrishna86 — Thu, 25 Aug 2016 05:56:08 GMT

Hi Nikitha,

Use the expression like below to convert SQL select statement to QlikView-

SQL SELECT "CUST_ID",

"ORDER_ID",

"SALE_DATE"

FROM Test.dbo.CUSTMERORDERS;

Thanks,

Ram Krishna