Help on Decision Tables

[Former Member]

Hi,

I'm currently putting together a decision table and have come across a bit of a challenge. I have a decision table that is setup as follows:

DECISION TABLE

Field 1	Field 2	Return Value
X	A	100
Y	B	200

I've applied some basic rules and the decision table in principle is working well. Where I've hit a brick wall is where I want to apply the return value to other rows in a table. The example I have is:

I have a table with the following rows:

FOUNDATION TABLE

Field 1	Field 2	Field 3
X	A	95
X	C	120
Y	B	300

The rule I'm trying to model is, if Field 1 is X and Field 2 is A then set the value for Field 3 to 100 for Rows where Field 1 is X and Field 2 is A, but also, set Field 3 to 100 where Field 2 is B. I've thought about transposing Field 2 to seperate columns in my foundation table, however, this would considerably widen my table.

Am I trying to model the impossible, or is there something that I am missing.

Thanks for any help.

Regards,

Gary

[rindia · Accepted solution]

Hi Gary,

One solution cam be to write the procedure with update clause as

UPDATE LAB_RSALLA.T1

SET FIELD3 = CASE  WHEN "FIELD1" = 'X' THEN

                         CASE WHEN "FIELD2" = 'A' THEN 100 ELSE FIELD3 end

                   WHEN "FIELD2" = 'B' THEN 200 ELSE FIELD3

                      END;

Above you can use the return value of decision table in place of 100 or 200.

Before update:                                                                

After update:

Regards

Raj

[Former Member · Accepted solution]

Thanks Raj. We need to keep the rules within the decision table as they can be frequently changed and the changes are being made by business users. We wan't to keep it as simple as possible without the need to change code if there's a change to the rule.

[Sesh_Sreenivas]

Hi Gary,

You can use 'OR' keyword while defining the Field 2 condition if the field 2.

Field 1	Field 2	Field 3
X	A or B	100

Hope that solves the problem

Regards,

Sesh

[Former Member]

Thanks Sesh. My initial explanation didn't explain all of the other conditions (I was trying to keep it simple). There are additional conditions that apply to the Row with A in Field 2 and those conditions for do not apply to the row contain B. So setting Field 2 to be A or B would not work unfortunately. From what I can tell so far, the rules that I've been given cannot be modelled in a decision table, which is unfortunate. Thanks for your help.

[Former Member]

My current line of thinking is that I'll create an additional action where we can specify which other rows for Field 2 should also have the value changed, then using the output we could create a script to do some post-processing of the data, giving us the output that we need. I'll give that a go and let you know how it goes.

[Sesh_Sreenivas]

Do you mean to say there are additional conditions apart from Field 1 and 2?

If you have some conditions that apply to the row with A, then you can create two rows like the one below. (right click on the condition cell where you have A, and from the context menu choose 'Add Condition Value')

Field 1	Field 2	Action
X	A B	100 200

If you can explain what is your actual scenario, maybe i can try to help you out. I am from the development team of Decision Tables on HANA and if your use case is so complex that we cant solve, we can take that as a feedback. So feel free explain your use case.

[Former Member]

Hi Sesh, thanks for the response. I'm speaking to the right person. I was trying to keep things simple in my initial post. The following describes one of our slightly complex rules in more detail. We have the following rule in the decision table:

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6 (Action)
AAA	BBB	>85 AND < 35	> 10	X	100

If the above conditions are met, then we need to update Field 6 to 100 for the following rows in the foundation table including rows with a Field 5 value of Y.

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6
AAA	BBB	40	15	X	100
AAA	BBB	45	5	Y	100

The problem is updating the second row in the foundation table. You will see that field 4 doesn’t meet the condition of the initial rule, so setting the condition for Field 5 to X OR Y in the decision table would not work in this instance as Field 4 is < 10.

We have a lot of rules to implement and they all follow this type of logic. We could quite easily implement these using a procedure, however, we want to keep them dynamic as it will be business users making the changes and they can change frequently.

I’m currently experimenting with an option to implement an additional action item that we would use to update the data post-processing via a procedure. The decision table would look like this:

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6 (Action)	Field 7 (Action)
AAA	BBB	>85 AND < 35	> 10	X	100	X, Y

This would still allow business users to update their rules via excel without a need for coding. Ideally I’d like to avoid using a procedure to keep things simple, but at the moment I can’t see any other way to do it.

[Sesh_Sreenivas]

Hi Gary,

I still dont get your actual rule use-case since the explanation is targeting a specific record. Do you want to update a specific record. If yes, how do you identify it? If you can identify them, then you can create specific conditions for them

But i can provide recommendation for the example that you have given. You can create a decision like the one below. Note that the field 5 is moved to the left of field 4.

Field 1	Field 2	Field 3	Field 5	Field 4	Field 6
AAA	BBB	> 85 AND < 35	X or Y	> 10 <= 10	100 150

Targeting a specific record

Field 1	Field 2	Field 3	Field 5	Field 4	Field 6
AAA	BBB	> 85 AND < 35	X or Y	> 10 5	100 150

I know this is like providing solution to target a specific record in the table (since i know what is it value ), but that is the information which i have.

Regards,

Sesh

[Former Member]

Hi Gary,

In the decision tables you can use the rules as

Field 1	Field 2	Field 3
X	A	100
*	B	100

Hope this helps.

--

Shreepad

[Former Member]

Thank's Shreepad, This would work also, however, if we had a case of Field 1 being Y and Field 2 being B, then the value for that row would also changed. I'm beginning to get the impression that decision tables cannot handle complex rules.

[rindia]

In that case my code is also not correct.

Decision table has to be created where attribute(field3) is used as action so that update happens directly on the table, which is what Shreepad suggested and it needs advanced modifications as per your requirements.

Seshadri Sreenivas is a Decision table developer in HANA and he might help.

Regards

Raj

[Former Member]

Hi Gary,

The Decision table works in a sequential manner.

For the condition of Field 1 = Y and Field 2 = B

if you want that to be 200, then it can be added before *,B,100

In case you want the value be unchanged for Y,B; you should set it to Initial value(Right Click Set to Initial)

You may even find even more methods to achieve exact solution

http://help.sap.com/hana_platform > HANA Developers Guide > Setting up the Analytical Model > Creating Decision Tables(Page 245)

--

Shreepad