Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to find a approach to write chart level expression based on below given SQL query.
SELECT COUNT(OBJ.ENTER_DT), ROUND(ABS(ENTER_DT - CLOSE_DT)/30,2) AVG_MONTHS, TO_CHAR((TO_DATE(CLOSE_DT)),'MON-YYYY') MONTH
FROM OBJECT OBJ
WHERE OBJ.CLOSE_DT < :P_DATE
AND CLOSE_DT BETWEEN '01-JAN-2014' AND '30-SEP-2014'
AND OBJ.ENTER_DT BETWEEN :P_DATE_FROM AND :P_DATE_TO;
Request your help to convert the sql query into a chart level expression.
Regards..
Dear Akash,
Convert your SQL Query to QVD and link this table to master table .
Then ,take this field as your expression. Eg : count(your filed).
Regards
Krishna
Your question looks incomplete.... Kindly provide some information about what type of chart you want to create....
You can directly write SQL Query to your Script and after running the script all fields would be available to use in QV Apps...
at high level
1. Create the Oracle OLE DB Connection(check whether you have the Oracle OLEDB Driver installed)
2. Load the raw data in Qlikview by scripting
for eg
STATS:
SQL SELECT OBJ.ENTER_DT as enter_dt,
CLOSE_DT,
FROM OBJECT OBJ
WHERE OBJ.CLOSE_DT < :P_DATE
AND CLOSE_DT BETWEEN '01-JAN-2014' AND '30-SEP-2014'
AND OBJ.ENTER_DT BETWEEN :P_DATE_FROM AND :P_DATE_TO;
Note: you have to pass the correct date(format) in the where
3. Create a Dimension as Month and then create the expressions for count and Average.
Hope it helps,
-Ram
Dear Krishna,
Thanks for your response. I have the QVD for the table "OBJECT" which is the Master table.
Based on this QVD, I need to define the expression in Bar chart to get the required values.
For ex. for the query, "ROUND(ABS(ENTER_DT - CLOSE_DT)/30,2) AVG_MONTHS " how I would be defining the expression at Bar chart level instead of script level.
Regards..
Hi Manish,
I want to define the expression for Bar chart instead of defining it at script level. Is there a way that allows me to convert the SQL query into report level expression like the one I have stated in my post?
Regards..
Hi Ramco,
For your suggested points..
1) The DB connection string exists.
2) I have fetched all the required table and stored it in the QVD format in one application.
3) Would request to know if I can define the posted SQL query as expression in bar chat instead of using in script?
Regards..
Dear Akash,
I'm saying Convert your Below query as a QVD.Like
Query :
SELECT COMP_CODE, COUNT(OBJ.ENTER_DT), ROUND(ABS(ENTER_DT - CLOSE_DT)/30,2) AVG_MONTHS,TO_CHAR((TO_DATE(CLOSE_DT)),'MON-YYYY') MONTH
FROM OBJECT OBJ
WHERE OBJ.CLOSE_DT < :P_DATE
AND CLOSE_DT BETWEEN '01-JAN-2014' AND '30-SEP-2014'
AND OBJ.ENTER_DT BETWEEN :P_DATE_FROM AND :P_DATE_TO;
STORE Query into D:\Query.QVD;
Drop Table Query;
After loading this you will get fields in QVD as COMP_CODE ,Month....
Link this table to Master Table by COMP_CODE.
Take Dimension in your chart from master table and write your expression Count(your field)
Try this procedure.If you have any doubts let me know.
Regards
Krishna
you can write like this to get absolute number and round to 2 digits.
=ROUND(FABS(ENTER_DT - CLOSE_DT)/30,0.2)
Hi Ankur,
No you can put the SQL Query in xpression. With the SQL Query you can load data via QVScript, build the DATA MODEL and build the expression based the data model in the Qlikiew objects like Chart, Text Box etc.
-Ram