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

New to Alteryx - Need help with the best approach

bilal0310
6 - Meteoroid

Hello - I'm new to this community so please forgive me if i'm posting this in the wrong place. 

 

i'm trying to analyze data below (only first few rows of millions of rows). What I'm trying to achieve here is if the account number, item number , contract type are same and the price is different I want to rename one of the rows in the contract type as CONTRACT1

 

 

ACCOUNT_NUMBERITEM NUMBERPrice (USD)Contract Type
1000035526212.34CONTRACT
1000035526210.45CONTRACT
1000033362311.51CONTRACT
100003336239.53CONTRACT
1000039952614.58CONTRACT
1000039952612.03CONTRACT
1000039952818.12CONTRACT
1000039952815.01CONTRACT
10000310010515.02CONTRACT
10000310010513.24CONTRACT
10000310010513.24NON CONTRACT
10000310010915.7CONTRACT

 

 

This is how I want my data set to look like

 

ACCOUNT_NUMBERITEM NUMBERPrice (USD)Contract Type
1000035526212.34CONTRACT
1000035526210.45CONTRACT1

 

 

Any help would be appreciated. 

3 REPLIES 3
MichalM
Alteryx
Alteryx

@bilal0310 

 

I've split the process into couple of steps so it's easier to understand

 

Step1 - Generate a sequence number (I call it OrderNo) for number of occurrences where Account_Number, Item Number and Contract Type are the same starting from 0.

I use the Multi-Row Formula in the below configuration to achieve this. 

 

Group by the three values you'd like to be the same. The formula will restart for every new "group".

 

Group by the three columns you'd like to be the same. The formula will restart for every new "Group"Group by the three columns you'd like to be the same. The formula will restart for every new "Group"

 

 

If IsEmpty([Row-1:OrderNo]) THEN 0
ELSE [Row-1:OrderNo]+1
ENDIF

 

 

Step2 - Use the Formula tool to create a new Contract type column by concatenating the original Contract type and OrderNo fields if the OrderNo is not 0.

 

 

IF [OrderNo] = 0 THEN [Contract Type]
ELSE [Contract Type]+ToSTring([OrderNo])
ENDIF

 

 

Example attached.

NickSm
Alteryx
Alteryx

@bilal0310  - Should be doable with a multi-row tool and grouping based on account number, item number, and contract type.  Can essentially create a grouped RecordID that you can add to your contract type column.

 

Attaching a quick sample.

bilal0310
6 - Meteoroid

You are a life saver! thank you so much !!

Labels