Multi Row formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi All,
There is a minor hiccup that I am facing in trying to create a logic. I would be glad if anyone can help me on this. The requirement is such that, I am sure I need Multi-Row Formula to fix it, but somehow i am unable to nail the formula.
Let us say we have three columns as, Name, Month, Gender.
Name | Month | Gender |
Tom | July | |
Tom | August | M |
Tom | September | |
Lucy | May | |
Lucy | June | |
Lucy | July | F |
Lucy | August | |
Lucy | September |
In the data shown above, i would like to fill the Gender column for each Name if there is an entry in any single row, similar to one shown below.
Name | Month | Gender |
Tom | July | M |
Tom | August | M |
Tom | September | M |
Lucy | May | F |
Lucy | June | F |
Lucy | July | F |
Lucy | August | F |
Lucy | September | F |
Thanks
Solved! Go to Solution.
- Labels:
- Preparation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
For the multirow formula to work you need to ensure the value populated is at the top. So use a sort tool to sort by name ascending and the gender descending (or whichever way brings the filled in gender value to the top line).
You should choose the 'update existing field' in the multirow formula tool and select gender.
The statement you want is relatively simple.
IF ISNULL([Gender]) THEN [Row-1:Gender] ELSE [Gender] ENDIF
You should also include name as a group by field.
An alternative method would be to add a filter tool and remove instances where gender is not NULL. Take this stream and use a join tool to bring the value against the entire data stream using name as the join field.
Ben
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks BenMoss for the reply. This worked perfectly fine. I did notsort the data before applying the formula using MultiRow Formula, which is the key.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That's great to hear @Zaid!
Would you mind marking the post as a solution just so this post is prioritised when people are searching for similar issues.
Ben
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can simply configure a formula tool for the column GENDER and using the below expression can solve the problem.
IF [Name] = "Tom" THEN "M" ELSEIF [Name]="Lucy" THEN "F" ELSE [Gender] ENDIF