match GL expense with transcations

[Jocelynupup]

Hi,

My goal is to match the GL and the transaction details, ensuring all transactions are recorded in GL. If not in GL, it kicks out and analyst will need to research.

Based on it, I create my input Table 1 which records transaction details, input Table 2 which is the general ledger, and the expected result.

My problem is the general ledger only records expense, so there is no identifier (for example, store, transID, gift card number) that I can use to match except the dollar amt. when customers come to the store, most people bought 25 or 50 gift cards. it's too ambiguous just to use dollar amt to do the matching. I have no any ideas on this. Is it possible do it in Alteryx? 

input 1: 

trans	locID	POS	AMT	DT
trans	1	Issue GC	50	10/27/2023
trans	2	Issue GC	25	10/27/2023
trans	3	Issue GC	25	10/27/2023
trans	4	Issue GC	25	10/27/2023
trans	5	Issue GC	50	10/27/2023
trans	6	Issue GC	50	10/27/2023
trans	7	Issue GC	75	10/27/2023
trans	7	Issue GC	75	10/27/2023
trans	7	Issue GC	75	10/27/2023
trans	8	Issue GC	25	10/27/2023
trans	8	Issue GC	25	10/27/2023
trans	9	Issue GC	25	10/27/2023

input2:

GL	Year	Period	Location	Amount	GL_Date
GL	2023	11	expense	-50	10/27/2023
GL	2023	11	expense	-25	10/27/2023
GL	2023	11	expense	-25	10/27/2023
GL	2023	11	expense	-25	10/27/2023
GL	2023	11	expense	-50	10/27/2023
GL	2023	11	expense	-50	10/27/2023
GL	2023	11	expense	-225	10/27/2023
GL	2023	11	expense	-50	10/27/2023

Result: 

GL	Year	Period	Location	G/L Amt	GL_Date	trans	ID	POS	trans AMT	DT
GL	2023	11	expense	-50	10/27/2023	trans	1	Issue GC	50	10/27/2023
GL	2023	11	expense	-25	10/27/2023	trans	2	Issue GC	25	10/27/2023
GL	2023	11	expense	-25	10/27/2023	trans	3	Issue GC	25	10/27/2023
GL	2023	11	expense	-25	10/27/2023	trans	4	Issue GC	25	10/27/2023
GL	2023	11	expense	-50	10/27/2023	trans	5	Issue GC	50	10/27/2023
GL	2023	11	expense	-50	10/27/2023	trans	6	Issue GC	50	10/27/2023
GL	2023	11	expense	-225	10/27/2023	trans	7	Issue GC	75	10/27/2023
GL	2023	11	expense	-225	10/27/2023	trans	7	Issue GC	75	10/27/2023
GL	2023	11	expense	-225	10/27/2023	trans	7	Issue GC	75	10/27/2023
GL	2023	11	expense	-50	10/27/2023	trans	8	Issue GC	25	10/27/2023
GL	2023	11	expense	-50	10/27/2023	trans	8	Issue GC	25	10/27/2023

Kickouts: 

trans	locID	POS	AMT	DT
trans	9	Issue GC	25	10/27/2023

Thanks,

[Raj]

Sol

[Qiu]

@Jocelynupup 
I think the answer to your question is not definite...

locID  3 can also be kickouts, right?

Would that be acceptable?