Need solution to solve a problem. Kind of Advanced Xlookup

[anandakrishnan]

Below is Ledger Table: (Input 1)

LDate	LCR	LDR
01-01-2022	100
02-01-2022	650	750
03-01-2022	200	1100
05-01-2022	300	1000
07-01-2022	400	900
09-01-2022	100	800
11-02-2022		1200
12-02-2022	300	1100
13-02-2022		1000
14-02-2022	900	400
14-02-2022	950	850
15-02-2022	100	1200
16-02-2022	200
17-02-2022	300	1000

Below is Statement Table (Input 2):-

SDate	SDR	SCR
05-01-2022	100
06-01-2022	200	1100
07-01-2022	300	1000
07-02-2022	400	900
08-02-2022	100	800
15-02-2022		1200
16-02-2022	300	1100
17-02-2022	800	1000
18-02-2022	900	400
15-03-2022	100	1200
16-03-2022	200	1100
17-03-2022	300	1000

DESIRED OUTPUT 1:

LDate	LCR	SDate	SDR
01-01-2022	100	05-01-2022	100
02-01-2022	650	NOT FOUND
03-01-2022	200	06-01-2022	200
05-01-2022	300	07-01-2022	300
07-01-2022	400	07-02-2022	400
09-01-2022	100	08-02-2022	100
12-02-2022	300	16-02-2022	300
14-02-2022	900	18-02-2022	900
14-02-2022	950	NOT FOUND
15-02-2022	100	15-03-2022	100
16-02-2022	200	16-03-2022	200
17-02-2022	300	17-03-2022	300

DESIRED OUTPUT 2:

LDate	LDR	SDate	SCR
02-01-2022	750	NOT FOUND
03-01-2022	1100	06-01-2022	1100
05-01-2022	1000	07-01-2022	1000
07-01-2022	900	07-02-2022	900
09-01-2022	800	08-02-2022	800
11-02-2022	1200	15-02-2022	1200
12-02-2022	1100	16-02-2022	1100
13-02-2022	1000	17-02-2022	1000
14-02-2022	400	18-02-2022	400
14-02-2022	850	NOT FOUND
15-02-2022	1200	15-03-2022	1200
17-02-2022	1000	17-03-2022	1000

Here I have two Input Tables Ledger and Statement, I want to compare the Ledger table with the statement table to get the ouptut as mentioned above.

OUTPUT1: Compare the Ledger col LCR with Statement Col SDR. Also first match should not come in next row comparison.

OUTPUT2: Compare the Ledger col LDR with Statement Col SCR

CAN ANYONE HELP TO SOLVE THE SAME USING ALTERYX. I tried but not getting desired output.

[geraldo]

@anandakrishnan 

An example follows.
make the adaptation to excel to see if the result is as expected.

[anandakrishnan]

@geraldo 

Thanks for turning up for the support.

But its not coming as a desired output. I want to check the Ledger Table with the Statement Table. 

If Ledger LCR values present in the Statement SDR then it should return the SDR value and SDate, also

for ex: if LDR = 100 on 01/03/2023 and if a value (i.e, SDR =100) is present on Statement table on or after 01/03/2023 which comes first should return. ie, if one more LDR of same value (i.e, LDR =100) is present on 05/03/2023 and in statement table of SDR = 100 on 02/03/2023 and SDR = 100 on 08/03/2023, then it should return  SDR= 100 of o8/03/2023.

Hope its clear.

[FinnCharlton · Accepted solution]

Hi @anandakrishnan , this will do the job for you:

In the first step, we generate one row for each date between the LDate in the leger, and the maximum SDate in the statement. This allows us to join LDates to all SDates that come after them:

Next, we perform left joins to match LDates and ledger numbers with all SDates in the future with matching statement numbers:

Next, we mark any LDates that didn't join with 'NOT FOUND':

Lastly, we use an iterative macro to correctly assign each ledger / statement combination:

Hope this helps!

[geraldo]

@anandakrishnan 

Check this new version for output 1. I include the input with excel

[anandakrishnan]

@FinnCharlton 

Thanks for the solution. The matching part is ok, I think the un-match cases ("Not Found Ones) need some updates to0, will again connect with you after trying with multiple cases.

[anandakrishnan]

@geraldo 

Thanks for putting effort and for helping.

But some false result is coming. All matches plus some other matches even if it is not present for ex if a same amount is present in ledger table for every month, but in statement only 11 months have the transaction. The current solution gives as 12 month match. 

Thanks.