topic Re: creating custom function in QlikView

creating custom function

Fri, 28 Oct 2011 04:48:57 GMT

Hi,

How to create a custom function in qlikview wherin I want to pass two paramaters...both are dates...and the function should check these dates in another table and should return the count of dates falling between two dates passed through function.

Any help would be very appreciated.

Thanks

Re: creating custom function

Fri, 28 Oct 2011 06:06:30 GMT

In Module editor (CTRL+M), custom functions can be created. Below is the syntax, but the scripting should be in VB/J Script.

Function <FunctionName> (<params>)

<code>

End Function

Regards,

Krian.

creating custom function

Fri, 28 Oct 2011 06:47:55 GMT

Kiran thanks for your reply.

Actually i am aware about this...my main concern is how to develop such function...if you could help me out with coding it would be very helpful.

creating custom function

Fri, 28 Oct 2011 12:18:48 GMT

Yogesh,

Normally the macro functions are written for functionaliy external to qlikview. For example to encypty or forecasting with custom algorithm etc. Your requirment is internal to qlikview; I mean to make calculation using Qlikviews data model. This can be done with qlikview functions.

If you are looking for reusablity of this there is a workaround. Create a variable and which can do the dynamic computation.

Hope this helps,

Kiran.

creating custom function

swuehl — Fri, 28 Oct 2011 14:13:05 GMT

Maybe you could use dollar sign expansion with parameter, check out the help for dollar sign expansion:

It will look like this

set MUL=’$1*$2’;

let X=$(MUL(3,7)); // returns 21 in X

Hope this helps,

Stefan

creating custom function

Sat, 29 Oct 2011 06:30:02 GMT

Yes i am looking for reusabilty of function.

My data is in below format:

Policy_NO Date1 Date2

1 2011-10-15 2011-10-25

2 2011-10-17 2011-10-28

Now for each row I will pass date1 and date2 in a function which will check these dates in another loaded table and will return the count of date falling between these two dates.

So my main requirement is creation of such a function which will perform the above task.

Rather than creating a function if there is any other way then plz do let me know.

Thanks

Re: creating custom function

swuehl — Sat, 29 Oct 2011 11:27:49 GMT

Maybe you don't even need a function to fulfill your requirement, an object expression might do just fine.

Could you describe a bit more what the context is, where do you want to do the count (in script or in a chart object), and how those two tables are linked (by Policy_NO ?).

For example, if you have linked the two tables by Policy_NO and use this field as dimension in a table chart, you could maybe use something like

= count( if( Date1 <= Date3 and Date3 <=Date2, Date3))

where Date3 is the date you want to check in the other table. You could probably replace above expression with a set expression also.

Anyway, it would be easier to help you creating a function or expression or script snippet, if you could precise your setting and requirement.

Regards,

Stefan

creating custom function

Sat, 29 Oct 2011 12:55:49 GMT

I want to do this calculation in script. I cant do it in front end. There is no link between the two tables. The other table is just an inline table wherin i will manually insert the dates provided. The inline table only contains 1 field i.e is list of dates.

creating custom function

swuehl — Sat, 29 Oct 2011 14:05:39 GMT

So maybe you could achieve this with interval match and something along this:

DatesTable:

LOAD * INLINE [

Dates

2011-10-16

2011-10-25

2011-10-27

2011-10-26

];

Policies:

LOAD * INLINE [

Policy_NO , Date1 , Date2

1, 2011-10-15 , 2011-10-25

2 , 2011-10-17 , 2011-10-28

];

tmpPolicies:

NOCONCATENATE LOAD * resident Policies;

inner join(tmpPolicies) Intervalmatch (Dates) LOAD Date1, Date2 resident tmpPolicies;

left join (Policies) LOAD

count(Dates),Policy_NO resident tmpPolicies group by Policy_NO;

drop table tmpPolicies;