topic Re: Adding SQL function into Qlikview in QlikView

Adding SQL function into Qlikview

Wed, 26 Nov 2014 10:51:31 GMT

Hi,

I have a SQL function that's used to get age bands for projected age data. So, how many members are in a 30-39 bracket in Nov 2014, Dec 2014 and so on. Code is below

DROP FUNCTION f_get_age_at;

Create or Replace Function f_get_age_at(

birthdate in Date, month In Date)

Return Varchar2 Is

ln_age_band varchar2(50);

age number;

Begin

age := floor(months_between(month,birthdate)/12);

ln_age_band := case

when age between '0' and '17' or age<0 then '0-17'

when age between '18' and '21' then '18-21(Student Rate)'

when age between '22' and '29' then '22-29'

when age between '30' and '39' then '30-39'

when age between '40' and '49' then '40-49'

when age between '50' and '59' then '50-59'

when age between '60' and '64' then '60-64'

when age between '65' and '69' then '65-69'

when age between '70' and '74' then '70-74'

when age between '75' and '79' then '75-79'

when age between '80' and '84' then '80-84'

when age between '85' and '89' then '85-89'

when age between '90' and '94' then '90-94'

else '>95'

END;

Return ln_age_band;

End;

Is there anyway, I can get that to run in Qlikview, by either converting it, or being able to just add it in, and have it called when Qlikview is reloading? It's not stored anywhere other than locally, so it's ran just before we run the query to get projected ages, so I can't call it externally.

Any help would be appreciated.

Re: Adding SQL function into Qlikview

simondachstr — Wed, 26 Nov 2014 10:53:37 GMT

You can use Interval match or maybe even the class function.

Re: Adding SQL function into Qlikview

giacomom — Wed, 26 Nov 2014 11:11:13 GMT

Hi Craig,

you can write your custom functions in VBScript or JScript using the Edit Module dialog and call them in your Main script when you reload the document.

Regards

Giacomo

Re: Adding SQL function into Qlikview

vinay_hg — Wed, 26 Nov 2014 11:13:13 GMT

in this scenario no need to go with this function, intervalmatch function is made for data bucketing

Re: Adding SQL function into Qlikview

Anonymous — Wed, 26 Nov 2014 11:38:38 GMT

Hi Craig,

Yes you can use Class function(Regular buckets) and Interval function(irregular Buckets)

Here is post by Henric:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/14/

Hope this helps

Regards

Neetha