HANA model - Count based on input parameter value

[ssurampally]

Hi,  I have got a requirement to calculate the count based on input value from user, the required 2 columns are created and posted.

Order Number	Created on	Posting date	Created	Posted
400012	01.12.2015	01.01.2016	?	?
400014	01.12.2015	01.12.2015	?	?
400015	01.06.2016	01.06.2016	?	?

user selection is Year,   there is no column which stores the year data.

It is going to be an input parameter/variable given to user for input,  based on the selection value, created and posted columns are to be calculated.

the calculation is,   if the given user value is 2015,   "Created" would be number of orders which have created on date falls in 2015. similarly for "posted" as well.

In the above example data,   if user input is 2015,  then Created - 2   posted - 1.

I am thinking, what is the appropriate way to achieve this,    should I be able to achieve it through 'Counter' or "new calculated column" and how?

Please let me know,

Thanks

Sreekanth

[pfefferf · Accepted solution]

One option would be the following:

• Create a projection node consuming the data from the table (or whatever your data source is).
• Create a column engine filter expression like following (IP_YEAR is the input parameter, column names of your date columns have to be correctly set):
  string(component("CreatedOn",1)) = '$$IP_YEAR$$' or string(component("PostingDate",1)) = '$$IP_YEAR$$'
• Create a calculated column "Created" with type Integer. The column gets a 1 if the year matches the input parameter, a 0 if it does not match. This allows a "counter aggregation". The column engine expression is like following:
  if(string(component("CreatedOn", 1)) = '$$IP_YEAR$$',1,0)
• Create a second calculated column "Posted" with type Integer. I gets the same expression than the "Created" calculated column, of course refering to the "PostingDate" column.
• Next create an aggregation node on top of the projection and add the calculated columns as aggregated columns to the output (with aggregation method "sum").

Regards,

Florian

[ssurampally · Accepted solution]

Thank you Florian, that helps to continue further. in my initial projection node table, data exists for several years. we do not want to project the all the years, but just the user input value and previous year.

how do I filter the year column in the projection node with User input Year and also the previous year value?

I am Bex developer, I used to write a customer exit program to read the data from IP1(user input), do the offset in code to add previous year also. Then customer exit input parameter is used to filter the data.

how do we do this in HANA native model?  should I use stored procedure to read the IP1 value and get the previous year in the logic?  if that is the case, how do I access the IP1 value in Stored procedure?

Please let me know, 

[pfefferf · Accepted solution]

To consider also the previous year you only have to adjust the filter expression and the expression of the calculated column. In the following examples the "in" function is used to compare the column against the entered year value and the calculated previous year value.

Filter expression:

in(string(component("CreatedOn",1)) ,'$$IP_YEAR$$',string(int('$$IP_YEAR$$')-1)) or in(string(component("PostingDate",1)) ,'$$IP_YEAR$$',string(int('$$IP_YEAR$$')-1))

Expression for calculated column (for instance for the CreatedOn column):

if(in(string(component("CreatedOn", 1)) ,'$$IP_YEAR$$', string(int('$$IP_YEAR$$'))-1),1,0)

Regards,

Florian