convert stored function into set analysis example

[#1]

I would like to know is there any example of convert stored function to set analysis.

Thanks.

[#2]

Hi,

What do you mean by converting stored function to set analysis?

[#3]

I have database stored function and want to implement the same logic using set analysis since it is fast. So i am trying to write the logic using set analysis.

Please let me know if you have any quesiton and thanks for your help.

[#4]

Hi,

I don't think there should be a need to implement db stored function to the set analysis.

my suggestions for you will be to first load data into Qlikview by using load statement.

if you are having stored function then you can directly fetch processed data through that stored function or else you can also first load all the data then you can write the script that will mimic the purpose served by the stored function in the DB.

to call a stored procedure you  just need to connect the DB through an ODBC connection then you can use

SQL execute proc_name

or

SQL call proc_name

if there are parameters needed then you can pass the parameters also.

but if that stored function is something where you can pass parameters dynamically and then generate a report or something like that, then you need to load all the needed data into qlikview and with that data you first make report and then those parameters can be given as data values in list boxes to be selected ny user.

Hope this helps!!

Let me know, if you need more clarification.

..

Regards,

Ashutosh Paliwal

[#5]

sorry for the confusion. I want to implement SF logic for dynamic calculations after loading data. When the end use select values from list box then i need to do some calculations using Set Analysis instead of calling DB SF every time, If i use SF it will take more time so I want to implement SF logic on Qlikview side using Set Analysis.

Hope you understand my question or let me know. Thanks.

[#6]

Hi,

I believe generally a SF logic will involve some complex join and other operations and handling those operations completely using set analysis could be quite a task (Also, how much it is possible that also is a question?).

Because, set analysis while being extremely flexible still works by selecting the values in the dimensions, it can not replicate all the ETL tasks.

So, Normally what I do in case when I have any stored procedure/function, I will replicate it most of the part in Qlikview scripting (in Data model), then the dynamic part will be handled in the front end.

So, for you also I would advise the same to implement it into the data modelling, and if there is some part which can not be done in the backend then go for front-end, while I would say that you will be able to replicate the functionality of the SF in the data model itself for most of the part.

If you have some sample SF then post here, I will try to show you some example to implement it in Qlikview, which should guide you for your actual SF implementation.

..

Ashutosh

[#7]

here is SF, i am trying to convert into SA. Please let me know if you have any questions.

CREATE FUNCTION dbo.fnGetABCAAmount

    (

      @ABC_ZZZD INT ,

      @ZZZZcode VARCHAR(5) ,

      @IsTransisitonedFacility BIT ,

      @DateOfService DATE

    )

RETURNS MONEY

AS

    BEGIN

        IF @DateOfService IS NULL

            SET @DateOfService = GETDATE();

        DECLARE @amount MONEY

        SELECT  @amount = ( ( ( WorkABC * WorkZZZ )

                            * ( CASE WHEN YEAR(@DateOfService) < 2006 THEN 1

                                     WHEN YEAR(@DateOfService) = 2007

                                     THEN 0.8994

                                     WHEN YEAR(@DateOfService) = 2008

                                     THEN 0.8806

                                     ELSE 1

                                END  -- Budget Adjustor Value

                                      ) )  + ( PEZZZ

                                           * ( CASE WHEN @IsTransisitonedFacility = 1

                                                    THEN TransitionedFacilityPExpenseABC

                                                    ELSE TransitionedNon_FacilityPExpenseABC

                                               END ) ) + ( MalpracticeABC

                                                           * MPZZZ ) )

                * ConversionFactor

        FROM    dbo.ABC_PprABC ABC JOIN

                dbo.ABC_GPC gpc ON ABC.ABC_HeaderID = gpc.ABC_HeaderID

                JOIN dbo.ABC_Header h ON gpc.ABC_HeaderID = h.ABC_HeaderID

                AND @DateOfService BETWEEN StartDate AND EndDate

        WHERE   ZZZZcode = @ZZZZcode

                AND ABC_ZZZD = @ABC_ZZZD

        RETURN CAST( @amount AS DECIMAL(10,2))

    END

go

[Qliker35]

Hi,

I have same issue.

I have stock data. Stock data will decrease according to orders with some calculations.

I tried to get over with set analysis but  i couldn't do it.

I accomplish it with stored procedure but when i change date in filter, my sp doesnt work.

I need to call stored procedure on set analysis.

Thanks for any advice