Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to know is there any example of convert stored function to set analysis.
Thanks.
Hi,
What do you mean by converting stored function to set analysis?
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.
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
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.
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
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
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