How to use a unique key from the output to match with the original data set with allocatio

[kristopherlam]

Hi all,

I have a set of data that contains multiple rows (>50K) and fields.

Date	Batch	Class	Amount
01/01/24	ABC123	AB	4400
01/01/24	ABC123	AC	5000
01/01/24	ABC123	AD	1000
02/28/24	ABC234	AB	6000
02/28/24	ABC234	AC	2000
02/28/24	ABC234	AD	1000

Using Class (e.g. AB) as the unique key to find out the batch that contains AB. (Result 1)  Afterward I wanna use the batch name to find out the whole set of data.

Date	Batch	Class	Amount
01/01/24	ABC123	AB	4400
02/28/24	ABC123	AB	6000

Allocate AB into the sum of amount of AC and sum of amount of AD based on the portion they have i.e. AC/(AC+AD) under same batch (to avoid wrong allocation into different date).

I try to use the unique tool to filter out AB under field "Class" but afterward I have no idea on how to do the matching back to whole set of data by using another key (batch name).

Thanks for the suggestion in advance! Due to the confidential issue, I cannot upload the table, sorry mate. 

[KGT · Accepted solution]

I'm not entirely sure exactly the output you want. I thought it may be the second table, but I don't know how that 2nd row is Batch:ABC123.

In any case, what you are most likely looking for is a mix of filter/summarize/join.

Filter to trim to the the class you want.

Summarize to get unique combinations of whatever you're after.

Join to use one dataset to filter the other.

If you want a formula to generate AC/(AC+AD) then cross tab followed by formula tool. (May need to replace Null() with 0 as the first formula.

[kristopherlam]

Great thanks! Appreciate your help.