topic DB Storedproc or function in QlikView

DB Storedproc or function

Thu, 03 May 2012 12:03:47 GMT

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

kaushiknsolanki — Thu, 03 May 2012 12:45:58 GMT

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

Thu, 03 May 2012 13:00:45 GMT

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

DB Storedproc or function

kaushiknsolanki — Sun, 06 May 2012 07:57:23 GMT

Hi,

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

Regards,

Kaushik Solanki

DB Storedproc or function

Mon, 07 May 2012 23:24:31 GMT

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

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

DB Storedproc or function

Sun, 20 May 2012 14:08:27 GMT

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