Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi Niki,
Create a view using the above query and then you pull that query to qlikview.
EG:
CREATE VIEW <View_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 ;
***********************************
In qlikview : LOAD *;
SQL Select * from <View_Name>
Regards,
Manideep
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
Hi,
in this article i have there the answer for you...
Please proceed as acording to it.
HTH
André Gomes
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.
Hi Jonathan,
You are correct. My objective is the same...
Thank you....
It would be more helpful...if it is more elaborate.
Regards,
Niki