display G/L=0

[Jocelynupup]

Hi,

The below are my inputs, expected output and Alteryx workflow.

I am working on data validation between general ledger and detail transactions. My question is how to display the G/L part in result when net amt of a store is 0.

For example, the net amount of store 101 is 0, so there is nothing in the general ledger (impossible to book 0 in GL). When I do the join, these transactions netting 0 are in the right join. So, I need to add the GL part and use union tool to get the full list.

GL input:

Year	Period	Company	Code	Amount	GL_Date
2023	11	100	1001	40	10/27/2023
2023	11	100	1001	30	10/28/2023

transaction detail input:

ID	Store	Amount	Trans Date
1	101	(50.00)	10/27/23
2	101	50.00	10/27/23
3	102	20.00	10/27/23
4	102	(10.00)	10/27/23
5	102	30.00	10/27/23
6	103	40.00	10/28/23
6	103	(10.00)	10/28/23
7	104	(30.00)	10/28/23
7	104	30.00	10/28/23

output: 

Year	Period	Company	Code	Amt	GL_Date	ID	Store	Amount	Trans Date
2023	11	100	1001	0	10/27/23	1	101	(50.00)	10/27/23
2023	11	100	1001	0	10/27/23	2	101	50.00	10/27/23
2023	11	100	1001	40	10/27/23	3	102	20.00	10/27/23
2023	11	100	1001	40	10/27/23	4	102	(10.00)	10/27/23
2023	11	100	1001	40	10/27/23	5	102	30.00	10/27/23
2023	11	100	1001	30	10/28/23	6	103	40.00	10/28/23
2023	11	100	1001	30	10/28/23	6	103	(10.00)	10/28/23
2023	11	100	1001	0	10/28/23	7	104	(30.00)	10/28/23
2023	11	100	1001	0	10/28/23	7	104	30.00	10/28/23

Alteryx workflow:

[AndrewDMerrill · Accepted solution]

Here is a sample workflow that should meet you requirements: