Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Grouping and Updating Data

lrhatigan
6 - Meteoroid

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. 

 

 ColAColBColCCategory

1

abca123Cat1
2defe123 

3

abci456Cat1
4abca  
5abce123 

 

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.

2 REPLIES 2
AndrewBanh
9 - Comet

Hi @lrhatigan 

 

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

lrhatigan
6 - Meteoroid

Thanks @AndrewBanh  - appreciate the quick response and resolution. 

Labels