Grouping and Updating Data

lrb_ayx

Hi, 

I am trying to create a workflow which will categorize data and record the relevant category on the original file (all existing columns to be included). There will be various checks run through the workflow, tagging each category. 

	ColA	ColB	ColC	Category
1	abc	a	123	Cat1
2	def	e	123
3	abc	i	456	Cat1
4	abc	a
5	abc	e	123

From the above example, Row 1 and 3 are categorized as Cat1 as there are multiple values in ColC associated with 'abc'. Row 4 is not yet categorized as ColC does not have a value and Row 5 is not categorized yet as it is a repeat of Row 1, this will be captured and categorized separately when ColB is categorized.  

Any suggestions for the most efficient way to group and update the data would be appreciated.

AndrewBanh

Hi @lrb_ayx 

To start, I have tagged each record with the recordID tool so it is easier to follow, as I will be sorting the data.

I have sorted your data to group CoIA initially, and because you mentioned CoIC first, I chose to sort on that field next.

Next step is to use the multirow formula tool. Few things to note:

- In "Group by" section, select CoIA so the expression resets every time it encounters a new CoIA. 

- The expression initially accounts for the empty CoIC in record 4 of your input, and will flag any unique rows with 1 afterwards.

- Note that record 5 is null because it is a repeat in CoIC to record 1.

The expression used was: 

if isnull([Coic])
then null()
elseif [CoIC] != [Row-1:CoIC]
then 1
else null()
endif

Lastly, I used a formula tool to then add the "Cat" before each of the 1's.

I am unsure with how you want to treat record 2 as it's CoIA is different to the others. I have flagged it as Cat1, but will be able to change that according to your needs.

Workflow will be attached below

Hope this helped!

- Andrew

Categorising Data.yxmd