topic Re: To convert SQL Query into Expression for Bar Chart in QlikView

To convert SQL Query into Expression for Bar Chart

ankurakash — Sat, 11 Oct 2014 04:07:30 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

krishna20 — Sat, 11 Oct 2014 06:13:51 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

MK_QSL — Sat, 11 Oct 2014 07:44:09 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

Sat, 11 Oct 2014 07:49:01 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

ankurakash — Sat, 11 Oct 2014 14:11:33 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

ankurakash — Sat, 11 Oct 2014 14:16:36 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

ankurakash — Sat, 11 Oct 2014 14:24:20 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

krishna20 — Sat, 11 Oct 2014 15:40:09 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

Sat, 11 Oct 2014 16:19:09 GMT

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

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

Re: To convert SQL Query into Expression for Bar Chart

Sat, 11 Oct 2014 18:04:31 GMT

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

Re: To convert SQL Query into Expression for Bar Chart

maxgro — Sat, 11 Oct 2014 21:41:44 GMT

read the data from your db

QlikTable:

SELECT

ENTER_DT,

CLOSE_DT

FROM OBJECT OBJ

WHERE

CLOSE_DT BETWEEN '01-JAN-2014' AND '30-SEP-2014'

then add a bar chart

dimension month(CLOSE_DT) & '-' & year(CLOSE_DT)

expression avg(fabs(ENTER_DT - CLOSE_DT))

Re: To convert SQL Query into Expression for Bar Chart

ankurakash — Wed, 15 Oct 2014 01:02:11 GMT

Dear Krishna,

Thanks for the step by step solution. Your solution is helping me to come close to the desired result.

I would request your guidelines in one of the issue I am facing in getting the data correctly(in sequence) for my report.

The scenario is: The SQL query for which I have created the QVD also contains the Group By Clause where field "CLOSE_DT" is first converted to date and then to character using TO_CHAR function - Syntax posted below:

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;

GROUP BY TO_CHAR((TO_DATE(CLOSE_DT)),'MON-YYYY')

Now, when I create the report taking "MONTH" as Dimension, the sequence of month is coming in alphabetical order i.e. they are not coming in sequence.

Look forward for your response.

Regards..

Re: To convert SQL Query into Expression for Bar Chart

krishna20 — Wed, 15 Oct 2014 04:54:07 GMT

Dear Akash,

Please attach a sample file, which the query loaded as QVD .

Regards

Krishna

Re: To convert SQL Query into Expression for Bar Chart

ankurakash — Thu, 16 Oct 2014 16:18:11 GMT

Dear Krishna,

The sample data and attachment is shared as below:

1) I have a field "CLOSE_DT" under table "OBJ" on which Master Calendar is created based on the below given field in both the tables:

Year(CLOSE_DT) & '-' & Month(CLOSE_DT) as Key,

Please consider anything after double forward slash "//" as the comment area

Master Calendar has following fields:

Key // On which table OBJ is connected

Year // On On which table CLOSEOBJ_QUERY is connected (Creating Synthetic Key)

Month // On which table CLOSEOBJ_QUERY is connected (Creating Synthetic Key) - Image shared above point2):

Quarter

2) Here, I created the report as per your suggestion: I generated a QVD based on the below given query.

CLOSEOBJ_QUERY:

SELECT SUM(ROUND(ABS(ENTER_DT-CLOSE_DT)/30,2))/COUNT(*) AVG_MONTHS,

TO_CHAR((TO_DATE(CLOSE_DT)),'MON') Month,TO_CHAR((TO_DATE(CLOSE_DT)),'YYYY') Year

FROM OBJ

WHERE CLOSE_DT IS NOT NULL

GROUP BY TO_CHAR((TO_DATE(CLOSE_DT)),'MON'),TO_CHAR((TO_DATE(CLOSE_DT)),'YYYY');

store CLOSEOBJ_QUERY into [$(path)\CLOSEOBJ_QUERY.QVD];

DROP TABLE CLOSEOBJ_QUERY;

3) At Script level, I called the newly created table:

CLOSEOBJ_QUERY:

LOAD AVG_MONTHS,

MONTH as Month, // Renaming it to Month so Master Calendar can be connected

YEAR as Year // Renaming it to Year so Master Calendar can be connected

FROM

[..\Data\QVD\CLOSEOBJ_QUERY.QVD]

(qvd);

//Here in the above script I have requirement of both the fields - Month and Year as Dimension for my report generation.

4) At report level, I have tried the following:

Line Chart Properties ->

Dimension -> Month; Year

Expression -> =SUM(AVG_MONTHS)

5) I get the desired result in my report but face two issues also depicted in the attached image:

a) Master Calendar - month is redundant and out of order // It is concatenating no. of months in the "Month" section in sheet area.

b) I am not getting proper sequence of month while depicting the line chart. Image attached.

Request your guidance in resolving this issue.

Regards..

Re: To convert SQL Query into Expression for Bar Chart

ankurakash — Thu, 16 Oct 2014 16:48:09 GMT

Hi Ram,

Thanks for responding. I am getting close to the desired result by creating QVD of the query and currently stuck in getting the report to be sync with the Calendar. For the same I have posted a reply to Krishna who is guiding me in fixing the issue. I look forward for your guidelines and approach to fix the current issue.

Regards..

Re: To convert SQL Query into Expression for Bar Chart

krishna20 — Fri, 17 Oct 2014 05:46:57 GMT

Dear Ankur ,

I didn't find any attachment with this thread.

Please answer for my doubts from your app.

1.Have you resolved the synthetic issue? If not resolve it ,by giving alias name to Month and Year.

2.If you got months in sequence will you reach your point??

3.Make sure the file has attached such that me or some one will reply you soon to resolve this issue.

Regards

Krishna