Example 1: filter with input parameter on numeric value

[jan_zwickel]

This example is referenced in my other post about input parameters. Please have a look at the other post to get a better understanding of the context for this example

 

We will use input parameter IP_1 to filter for records that have a productRating larger than 2.

As we will use the input parameter to filter on a numeric value the documentation tells us that the calling syntax should be (Note: you might sometimes find other syntax for passing input parameters. Please use this syntax with "=>". This is especially relevant when passing input parameters inside procedures - not discussed in these posts - as otherwise the activation might fail):

(placeholder."$$IP_1$$"=>2)

i.e., without enclosing the value 2 in apostrophes. This makes intuitively sense as we treat IP_1 as integer and not as string.

In addition, the documentation tells us that the expression during processing should also not include IP_1 in apostrophes:

"productRating" > $$IP_1$$

Again, it makes sense as we are not treating the value 2 as a string (and might want to evaluate, e.g., 1+1):

 

Here are the steps to model this requirement:

a) Create a Calculation View

b) Add table IPExample as a datasource

c) Map all fields to the output

d) Create direct input parameter IP_1 of type INTEGER (see screenshot below)

 



 

e) In the Aggregation Node enter the following filter (a graphical expression editor is also available):



f) Save, build and do a data preview on the view

g) When prompted for a value enter 2

You will now see only records that have a productRating higher than 2:



 

If you look at the respective SQL you will see that the expected placeholder syntax is used in the data preview query:

SELECT TOP 1000

"date",

"product",

SUM("productRating") AS "productRating",

SUM("amount") AS "amount"

FROM "INPUTPARAMETERS_HDI_DB_1"."inputParameters.db::example1"

  (placeholder."$$IP_1$$"=>2)

GROUP BY

"date",

"product";

 

 

Click here to navigate back to the context in which this example is discussed. You will also find further examples there.