journal workflow

[meghant03]

I have a column called "spreadsheet key" and another one called "company id", so every time the company id changes, I want the spreadsheet key to be unique for each company id. For example, if the company id is 10000 then the key should be 1 but if it changes to 10001 then the key should be 2 and so on. How do I achieve this in Alteryx?

[Bobbyt23]

You could summarise all your company id’s using group by in the summarise tool. This will give all the unique ones. Then put a multi row formula after called key which is a number value and the formula is just the previous row key+1. That will give unique keys per company id. Then you could join that back to your main data set to put the key back in that.

[meghant03]

Can you explain the multi-formula step in more detail I am kind of confused? I don't want there to be 2 "spreadsheet key" columns in the final output.

[KGT]

In the solution by @Bobbyt23, you don't need the multi-row, a recordID will work there. I like that solution because it also gives you your lookup table for Cmpany-Spreadsheet.

If you want to use a multi-row though, the solution can be easier.

Sort by CompanyID

Multi-row: IF [CompanyID]==[Row-1:CompanyID] THEN [Row-1:SpreadsheetID] ELSE  [Row-1:SpreadsheetID]+1 ENDIF

I'll add that you can cut out the IF condition and just have [Row-1:SpreadsheetID] with CompanyID selected as a grouping field. But that can be hard to troubleshoot later if the tick in that box is not clear (i.e. lots of fields with CompanyID not being in the first few).

[Bobbyt23]

Here is an example of both options @meghant03