topic convert stored function into set analysis example in QlikView

convert stored function into set analysis example

Mon, 21 May 2012 01:32:54 GMT

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

Thanks.

convert stored function into set analysis example

Mon, 21 May 2012 06:48:27 GMT

Hi,

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

convert stored function into set analysis example

Mon, 21 May 2012 11:26:00 GMT

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.

convert stored function into set analysis example

Mon, 21 May 2012 11:43:42 GMT

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

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

convert stored function into set analysis example

Mon, 21 May 2012 14:40:06 GMT

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.

convert stored function into set analysis example

Tue, 22 May 2012 09:44:04 GMT

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

convert stored function into set analysis example

Wed, 23 May 2012 15:15:07 GMT

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

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

Re: convert stored function into set analysis example

Qliker35 — Mon, 14 May 2018 08:09:40 GMT

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