Exclude specific rows

anujarora1

Hi Experts,

I have a scenario where I want to exclude certain rows from the data only to perform additional procedures and achieve an outcome based on the included rows. Later in my output I want to keep all the rows and assign a final value (in a new column) to all rows, even the excluded ones.

In the below table, I want to exclude any Fee or Tax Amount and include only Value (names may vary, can have multiple options). Then I compare all amounts within the Value Type (which is same in each case). Therefore, the final outcome in the column Output is "Fixed Amount" but now I want to put this in all columns (even the excluded Fee and Tax).

Thanks a lot!

ID	Type	Amount	Output (New Column)
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Value	500	Fixed Amount
1	Fee	10	Fixed Amount
1	Tax	5	Fixed Amount

danilang

Hi @anujarora1 

Here's one way to do it

After assigning a RecordID to use in the final sort, exclude all the Fee and Tax records.  Count number of distinct amounts grouped by ID.  If this is 1 then it's a Fixed amount.  If not, it's Variable.  Join this result back to your original data.  This assigns the Fixed/variable output to all the rows with the matching ID to give you 

Dan

ValueToAllRows.yxmd