8 Replies Latest reply: Oct 29, 2011 10:05 AM by Stefan Wühl RSS

    creating custom function

      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

          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

              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

                  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

                      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
                          Stefan Wühl

                          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

                              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
                                  Stefan Wühl

                                  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;

                      • creating custom function
                        Stefan Wühl

                        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