Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Oracle Function into Qlikview

Hii,

how we can impement the logic mentioned below into Qlikview. Below is the oracle function which uses the Cursors to do some calculations. Need to implement the same calculations in Qlikview.

CREATE OR REPLACE FUNCTION APPS.FUNCTION_NAME (

   month_frm   NUMBER,

   year_frm    NUMBER

)

   RETURN NUMBER

IS

   CURSOR cur_div

   IS

      SELECT DISTINCT division_code

                 FROM target_header

             ORDER BY 1;

   CURSOR cur (p_division NUMBER)

   IS

      SELECT DISTINCT division_code, ROUND (AVG (job_gp_pro), 5) avg_gp_pro,

                      month_no month_val, YEAR year_val

                 FROM target_header a, target_lines b

                WHERE a.header_id = b.header_id

                  AND month_no = month_frm

                  AND YEAR = year_frm

                  AND division_code = p_division

             GROUP BY division_code, month_no, YEAR

             ORDER BY 1;

   l_actual_val   NUMBER := 0;

   l_gp_val       NUMBER := 0;

   l_total_val    NUMBER := 0;

   l_actual_tot   NUMBER := 0;

   l_result_val   NUMBER := 0;

BEGIN

   FOR r_cur_div IN cur_div

   LOOP

      FOR r_cur IN cur (r_cur_div.division_code)

      LOOP

         BEGIN

            SELECT NVL2(SUM(pail.inv_amount),SUM(pail.inv_amount),1)--NVL (SUM (pail.inv_amount), 0)

              INTO l_actual_val

              FROM pa_draft_invoices_v pain, pa_draft_invoice_lines_v pail

             WHERE pail.project_id = pain.project_id

               AND pail.draft_invoice_num = pain.draft_invoice_num

               AND pain.project_organization_id = fnd_profile.VALUE ('ORG_ID')

               AND TRUNC (pain.gl_date)

                      BETWEEN TO_DATE (   '01/'

                                       || LPAD (r_cur.month_val, 2, 0)

                                       || r_cur.year_val,

                                       'dd/mm/rrrr'

                                      )

                          AND TO_CHAR

                                 (LAST_DAY (TO_DATE (   '01/'

                                                     || LPAD (r_cur.month_val,

                                                              2,

                                                              0

                                                             )

                                                     || r_cur.year_val,

                                                     'dd/mm/rrrr'

                                                    )

                                           )

                                 )

               AND pain.invoice_status_code = 'ACCEPTED'

               AND pail.invoice_line_type = 'STANDARD'

               AND pain.project_id IN (

                      SELECT ppa.project_id

                        FROM pa_projects_all ppa,

                             pa_project_classes ppc,

                             pa_project_players ppp

                       WHERE ppa.project_id = ppc.project_id

                         AND ppp.project_id = ppa.project_id

                         AND UPPER (ppc.class_category) = 'DIVISION'

                         AND SUBSTR (ppc.class_code, 1, 2) =

                                NVL (SUBSTR (r_cur.division_code, 1, 2),

                                     SUBSTR (ppc.class_code, 1, 2)

                                    ));

         EXCEPTION

            WHEN OTHERS

            THEN

               NULL;

         END;

         l_actual_tot := l_actual_tot + l_actual_val;

         l_total_val :=

                       l_total_val

                       + (l_actual_val * (r_cur.avg_gp_pro / 100));

         BEGIN

            l_result_val := (l_total_val / NVL (l_actual_tot, 1) * 100);

         EXCEPTION

            WHEN OTHERS

            THEN

               NULL;

         END;

      END LOOP;

   END LOOP;

   RETURN (ROUND (l_result_val, 5));

END;

6 Replies
Highlighted
Specialist II
Specialist II

You can easily call this function in a SQL query>>

SQL select Apps.function_name(10,13) from dual;

This will return a numeric value as returned by your function and you can store it in qvd or access it in qlikview.

Regards

Nitin

Highlighted
Master III
Master III

nilesh just stick up with this link .

It will help you .

http://community.qlik.com/thread/74382

Highlighted
Partner
Partner

Hii,

Yes Nitin, We can do that. but this will store only one value into Qvd. if we want it for all the months then what will be the logic.???

-Nilesh

Highlighted
Specialist II
Specialist II

Select year,month,

apps.function(month,year)

from table name;

This query will definitely provide your the desired results.

Keep a note that month and year should be numeric in your table also..

If not you can easily change it in oracle..

Regards

Nitin

Highlighted
Partner
Partner

HI,

if you can see in the above Query multiple tables are used, there is no specific table from which we can extract data.

hence we cant write the above Query.

is there any other way.

-Nilesh

Highlighted
Specialist II
Specialist II

I think I was unable to explain you correctly,

I think you need this calculation(via function) for each year month...

and you might be generating a table with year month as dimension and calcualted value as expression

For example>>

Create a calendar table which would definately contain month and year as....

Year        Month         Quarter      

2012         11                 Q4

2012         12                 Q4

2013         1                   Q1
2013         2                   Q1

2013         3                   Q1

2013         4                   Q2

Etc ....................

then you can use these columns(Year Month) in the query and you will easily get the calculated value for each year and month.

_______________________________________________________________________________________________

===================================================================================

I think , you also might be loading some other table which might contain year and month, then you can use those fields for the same.

function will not effect the output of you query , it will just add a column with your desired calculated value for each year and month......

Regards

Nitin