Data transformation

[RajatRehria]

Hi Guys,

I have a logic in a table below, I want to incorporate this logic in my main data set.

For example Combination 1 states if in my main dataset "Cost" column value = 109353 and "Profit" column value = 143085 then filter my main data as Required. Similarly each combination  conditions i need to incorporate in my main data set and lastly categorised them as Group 1 etc.

Groups	Combination	Column names	Sign	Description	Value
Group 1	1	Cost	=	is equal	109353
Group 1	1	Profit	=	is equal	143085
Group 1	2	Profit	>=	is greater or equal	114558
Group 1	2	Profit	<=	is smaller or equal	164925
Group 1	2	Cost	>=	is greater or equal	180052
Group 1	2	Cost	<=	is smaller or equal	133952
Group 1	3	Profit	=	is equal	176437
Group 1	3	Cost	=	is equal	166891
Group 1	4	Profit	=	is equal	144627
Group 1	4	Cost	=	is equal	198735
Group 1	5	Cost	=	is equal	166080
Group 1	5	Profit	=	is equal	130630
Group 1	6	Cost	=	is equal	129679
Group 2	1	Cost	=	is equal	197602
Group 2	1	Level0	<=	is smaller or equal	172640
Group 2	2	Cost	=	is equal	105669
Group 2	3	Level0	>	is greater	162398
Group 2	3	Cost	=	is equal	106784

Example: below is a snippet of my Raw input of main dataset

Cost	Profit	Level0
109353	143085	190734
102226	148795	189911

Required output after incorporating the above logic in the main data set:

Cost	Profit	Level0	Checks	Groups
109353	143085	190734	Required	Group 1
102226	148795	189911	Not required

Kindly help in suggesting a way using which i can incorporate the logic into my main data set. Thanks. 

[cjaneczko]

You should be able to create this logic in a Formula Tool. See the code below. Then add another formula that checks if the word Group appears in the Groups field. If it does then have it say Required, if not, then its Not Required. 

if [Cost] = 109353 and [Profit] = 143085 then 'Group 1' 
elseif [Profit] >= 114558 and [Profit] <= 164925 and [Cost]>= 180052 and [Cost] <= 133952 then 'Group 1' 
elseif [Profit] = 176437 and [Cost] = 166891 then 'Group 1' 
elseif [Profit] = 144627 and [Cost] = 198735 then 'Group 1' 
elseif [Cost] = 166080 and [Profit] = 130630 then 'Group 1' 
elseif [Cost] = 129679 then 'Group 1' 
elseif [Cost] = 197602 and [Level0] <= 172640 then 'Group 2' 
elseif [Cost] = 105669 then 'Group 2' 
elseif [Level0] > 162398 and [Cost] = 106784 then 'Group 2' 
else '' 
endif

if IsEmpty([Group]) then 'Not Required' else 'Required' endif

[RajatRehria]

Hi @cjaneczko thanks for a solution, however I am looking for a solution which can use the Logic Table as Text Input rather that creating a static formula. I am looking for a solution which can use the table as logic. Thanks.

[caltang]

It's hard to break the value of a record into its own operator. You can do it with "=", but ">" or "<" symbols will be difficult in this case. 

[cjaneczko]

I do not think it is possible to do what you are looking to do. You would have to append every condition to every row and check each condition one by one using a Boolean indicator. Then likely use a multi row formula to see where all rows for a condition = 1.