To convert SQL Query into Expression for Bar Chart

[ankurakash]

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

[krishna20]

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

[MK_QSL]

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

[#4]

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

[ankurakash]

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

[ankurakash]

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

[ankurakash]

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

[krishna20]

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

[#9]

you can write like this to get absolute number and round to 2 digits.

=ROUND(FABS(ENTER_DT - CLOSE_DT)/30,0.2)

[#10]

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