Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

DB Storedproc or function

I have a database stored function and I want to implement the same function on qlikview side for dynamic calculation when ever the user selection changes. so i would like to know the best option to handle this sceneario ie. write a vb script or setAnalysis.

Thanks

5 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    I would suggest you to use set analysis if you are able to achieve what you want with that.

    The reason is set analysis is very fast as compared to vb script code.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Let me try SA first then.. Thanks for your quick responce...

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

   Yes try and let us know if you have any more problem.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

I am trying to convert this fun to SetAnalysis and i was confused on how to convert WHERE conditions and input variable in this function. your suggestions are welcome...

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

Not applicable
Author

Any one has any hit on how to convert this SF to Set Analysis. Thanks for your time.