Hello community,
I am trying to create a Rank column using Multi-Row formula tool. I was able to do it, but my data has some changes and my current logic is not able to handle it.
Input Data
| JSON_Name | JSON_ValueString |
| Code2 | X12345 |
| Code3 | AA |
| Number | ABC12345 |
| Name | X12345AA |
| CompanyName | TEST |
| Code2 | Y12345 |
| Code3 | BB |
| Number | XYZ12345 |
| Name | Y12345BB |
| CompanyName | TEST |
| Code2 | Z12345 |
| Code3 | CC |
| Number | ZYX12345 |
| Name | Z12345CC |
| CompanyName | TEST |
| Code1 | NAC3AD |
| Code2 | 1234 |
| Code3 | AA |
| Number | A90112012 |
| Name | NAC3AD1234AA |
| CompanyName | TEST |
| Code1 | NAC3AD |
| Code2 | 4567 |
| Code3 | BA |
| Number | B90110312 |
| Name | NAC3AD4567BA |
| CompanyName | TEST |
Currently, I am using the Multi-Row formula using the formula mentioned below.
IF [JSON_Name] = "Code1" then [Row-1:Rank] + 1
ELSE [Row-1:Rank] ENDIF
There are some instances in the data where the Code1 field will not be available with the response from the API like shown in the above example. When I am using this formula, I am getting the below result which is causing me some data loss.
| JSON_Name | JSON_ValueString | Rank |
| Code2 | X12345 | 0 |
| Code3 | AA | 0 |
| Number | ABC12345 | 0 |
| Name | X12345AA | 0 |
| CompanyName | TEST | 0 |
| Code2 | Y12345 | 0 |
| Code3 | BB | 0 |
| Number | XYZ12345 | 0 |
| Name | Y12345BB | 0 |
| CompanyName | TEST | 0 |
| Code2 | Z12345 | 0 |
| Code3 | CC | 0 |
| Number | ZYX12345 | 0 |
| Name | Z12345CC | 0 |
| CompanyName | TEST | 0 |
| Code1 | NAC3AD | 1 |
| Code2 | 1234 | 1 |
| Code3 | AA | 1 |
| Number | A90112012 | 1 |
| Name | NAC3AD1234AA | 1 |
| CompanyName | TEST | 1 |
| Code1 | NAC3AD | 2 |
| Code2 | 4567 | 2 |
| Code3 | BA | 2 |
| Number | B90110312 | 2 |
| Name | NAC3AD4567BA | 2 |
| CompanyName | TEST | 2 |
Due to instances like these, I would like to use CompanyName as the identifier to increment Rank instead of prefix
Expected Output
| JSON_Name | JSON_ValueString | Rank |
| Code2 | X12345 | 0 |
| Code3 | AA | 0 |
| Number | ABC12345 | 0 |
| Name | X12345AA | 0 |
| CompanyName | TEST | 0 |
| Code2 | Y12345 | 1 |
| Code3 | BB | 1 |
| Number | XYZ12345 | 1 |
| Name | Y12345BB | 1 |
| CompanyName | TEST | 1 |
| Code2 | Z12345 | 2 |
| Code3 | CC | 2 |
| Number | ZYX12345 | 2 |
| Name | Z12345CC | 2 |
| CompanyName | TEST | 2 |
| Code1 | NAC3AD | 3 |
| Code2 | 1234 | 3 |
| Code3 | AA | 3 |
| Number | A90112012 | 3 |
| Name | NAC3AD1234AA | 3 |
| CompanyName | TEST | 3 |
| Code1 | NAC3AD | 4 |
| Code2 | 4567 | 4 |
| Code3 | BA | 4 |
| Number | B90110312 | 4 |
| Name | NAC3AD4567BA | 4 |
| CompanyName | TEST | 4 |
Can someone please help me with this?
Thank you
