5 Replies Latest reply: May 20, 2012 10:08 AM by PurnaC10 RSS

    DB Storedproc or function

    PurnaC10

      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

        • DB Storedproc or function
          Kaushik Solanki

          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

            • DB Storedproc or function
              PurnaC10

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

                • DB Storedproc or function
                  Kaushik Solanki

                  Hi,

                   

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

                   

                  Regards,

                  Kaushik Solanki

                    • DB Storedproc or function
                      PurnaC10

                      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