Loop for rule engine with new columns

[ashwan15hk]

I would like to have a loop for calculating the rule engine by variables, and the results will show each rule engine results as a new column.

Thanks!

Input:

Name	Born year
A	2007
B	2008
C	2009
D	2009
E	2009
F	2010
G	2011

Would like to have a output like this:

Name	Born year	2007	2008	2009	2010	2011
A	2007	Yes	No	No	No	No
B	2008	No	Yes	No	No	No
C	2009	No	No	Yes	No	No
D	2009	No	No	Yes	No	No
E	2009	No	No	Yes	No	No
F	2010	No	No	No	Yes	No
G	2011	No	No	No	No	Yes

[Aswin]

Alternative solution: use the Pivot node to put the years in separate columns, and Join the result of that with the original table.

Edit: Armin’s solution below is even simpler and more appropriate

[armingrudd]

Hi,

You can use “One to Many” node for this.
The output will be the same as you want except for the “Yes/No” values which will be “1/0” in this case but you can edit them and change the values from 1/0 to Yes/No later.

Best,
Armin

[ashwan15hk]

Thank you, and sorry about the confusion. Actually I would need to calculate exactly if the client is new or existing client when based on their purchase year, and I am using the rule engine. (first purchase year = purchase year => “New”).

I would like to get the results like this, so i can summarize how many new and existing clients for each year.

Name	First purchase year	Purchase year	2007	2008	2009	2010	2011
A	2007	2007	New
B	2008	2009			Existing
C	2009	2009			New
D	2009	2009			New
E	2009	2010				Existing
F	2010	2011					Existing
G	2011	2011					New

[armingrudd]

Well.
Here is the workflow to do this:

After the “One to Many” node we have a “Column List Loop Start” to loop over each year column and a “Column Expressions” to assign the value “New/Existing”. After the loop end we have a “Column Filter” node to exclude the duplicates of the first 3 columns(using regex).

Please let me know if this is what you need:
oneToMany.knwf (28.4 KB)

Best,
Armin

P.S. To have the year columns in order, you can use a “Sorter” node to sort rows based on the “purchase year” and then a “Domain Calculator” node (for the same column) just before the “One to Many” node.

[ashwan15hk]

Thank you Armin