creating custom function

[#1]

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

[#2]

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.

[#3]

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.

[#4]

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.

[swuehl]

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

[#6]

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

[swuehl]

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

[#8]

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.

[swuehl]

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;