Advanced Filters Syntax on Recipients Import

[Stefano_Dalla_Guarda]

Starting with 15.2, it's possible to import filters specifying the ConnectionName and isNumeric and Evaluate parameters. With 15.3.0.1, you can also import a field that seems numeric as text. This tutorial explains this new syntax.

Read "Filter Syntax, Parameters, and Options for Importing Recipients" prior.

NPrinting New Syntax Explanation

  

From NPrinting 15.2 and forward, you may use the following syntax:

[connectionName].[field] = {value1<isnumeric,evaluate>,value2<isnumeric,evaluate>,...}<drop,unlock,lock,verify,excluded>

• [connectionName] = name of the connection. By specifying this you can create different filters based on fields with the same names from different connections
• [field] = field name
• value1 = first value of the field to select. You can add as many values as you want
• <isnumeric,evaluate> = if one or both are specified, the correspondent flag will be activated
• <drop,unlock,lock,verify,excluded,clearselections> = filter options, the ones you add will be activated

The old syntax still works. If you use the old syntax you don't need to specify the name of the connection, isNumeric and Evaluate parameters.

    

Open the Excel Recipients Source File

            

                        

  

1. Go to C:\NPrintingTraining\Recipients
2. Double-click on RecipientsFiltersComplete.xlsx

Add a New Worksheet

            

                        

  

1. Click on the New Sheet icon.

    

Rename the Worksheet

            

                        

  

1. Rename it to isnumeric and evaluate import
2. Create a header row.

Create a New Filter

            

                        

  

1. Full Name: enter a persons name or other identifier
2. Filter1: enter Year={2013<isnumeric>,year(now())<isnumeric,evaluate>}<verify>

The other fields aren't mandatory.

If you don't specify the connection name, it will refer to the default connection. Entering this line of code, the Year field is set to two values:

• Numeric value set to 2013
• Formula "year(now())" set to isNumeric and Evaluate. This formula returns the numeric value of the current year. Evaluate is necessary to force the evaluation of the formula.

Verify filter option is activated.

Specify the Connection When Creating a New Filter

            

                        

  

1. Full Name: enter another persons name or other identifier
2. Filter1: enter [SalesDemo].[Year]={2013<isnumeric>,year(now())<isnumeric,evaluate>}<verify>

In the first part of this line of code the name of the connection of the Field is specified. By specifying this you can create different filters based on Fields from different connections.

Create Two New Filters Referring to Two Different Connections

            

                        

  

1. Full Name: enter another persons name, title or other identifier
2. Filter1: enter [SalesDemo].[Year]={2013<isnumeric>}<verify>, [Sales Compass].[Opp Close Year]={2013<isnumeric>}<verify>

In this case two Fields from two different connections are specified. With this new syntax it is possible to create filters based on fields from different connections.

   

Final Result

            

                        

  

After importing the Recipients, you'll find a new filter with two fields from two different connection.

Specify the Connection Name

  

Starting with 15.3, it's possible to explicitly set or unset a flag for the isNumeric and Evaluate parameters. Previous syntax will still work.

[connectionName].[field]={value1<isNumeric=yes,Evaluate=no>, value2<isNumeric=True,Evaluate=True>}

You can use "Yes" or "No" or the boolean values "True" or "False".

Explicit Set of isNumeric and Evaluate

            

                        

  

1. Full Name: enter another name or identifier
2. Filter1: enter Year={2013<IsNumeric=yes,Evaluate=no>, "Year(Now())"<IsNumeric=True,Evaluate=True>}

In the first part, the IsNumeric flag is explicitly set to Yes and Evaluate to No. In the second part the IsNumeric and Evaluate flags are both forced to True, which is synonymous with Yes. This means that the expression will be evaluated and return a numeric value.

Force a Value that Seems to be a Number to be Compared as a String

            

                        

  

1. Full Name: enter another persons name, title or other identifier
2. Filter1: enter CustomerIDText={0063<IsNumeric=no,Evaluate=no>}

The QlikView field CustomerIDText is created as a text field but it contains values that seem to be numbers. By inserting "IsNumeric=no" we are sure that the flag will be disabled and the value "0063" will be compared as a string.

Use double quotes (") to import filter values that contain commas

            

                        

  

1. Full Name: enter another persons name, title or other identifier
2. Filter1: enter Address={"2, rue du Commerce", "24, place Kléber"}

You can import filter values that contain commas (,) by enclosing them between double quotes ("). Using a comma outside will continue to be the separator between two value.

Use double quotes (") to import filter values that contain formulas with commas

         

1. Full Name: enter another name or identifier
2. Filter1: enter MonthNum = {"num(month(AddMonths(now(),-1)))"<isnumeric,evaluate>}

You can import filter values that contain formulas with commas (,) by inserting them between double quotes(").

Recipient import

            

                        

  

To import new Recipients refer to "How to Create and Import Recipients with Filters and Distribution Groups".

[AzerAskerov]

Great and clean explanation