Below is Ledger Table: (Input 1) LDateLCRLDR01-01-2022100 02-01-202265075003-01-2022200110005-01-2022300100007-01-202240090009-01-202210080011-02-2022 120012-02-2022300110013-02-2022 100014-02-202290040014-02-202295085015-02-2022100120016-02-2022200 17-02-20223001000 Below is Statement Table (Input 2):- SDateSDRSCR05-01-2022100 06-01-2022200110007-01-2022300100007-02-202240090008-02-202210080015-02-2022 120016-02-2022300110017-02-2022800100018-02-202290040015-03-2022100120016-03-2022200110017-03-20223001000 DESIRED OUTPUT 1: LDateLCRSDateSDR01-01-202210005-01-202210002-01-2022650NOT FOUND03-01-202220006-01-202220005-01-202230007-01-202230007-01-202240007-02-202240009-01-202210008-02-202210012-02-202230016-02-202230014-02-202290018-02-202290014-02-2022950NOT FOUND15-02-202210015-03-202210016-02-202220016-03-202220017-02-202230017-03-2022300 DESIRED OUTPUT 2: LDateLDRSDateSCR02-01-2022750NOT FOUND03-01-2022110006-01-2022110005-01-2022100007-01-2022100007-01-202290007-02-202290009-01-202280008-02-202280011-02-2022120015-02-2022120012-02-2022110016-02-2022110013-02-2022100017-02-2022100014-02-202240018-02-202240014-02-2022850NOT FOUND15-02-2022120015-03-2022120017-02-2022100017-03-20221000 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. Statement.xlsx Ledger.xlsx Desired Output.xlsx

Need solution to solve a problem. Kind of Advanced Xlookup

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.

Statement.xlsx

Ledger.xlsx

Desired Output.xlsx

Workflow

Accepted answers

FinnCharlton

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!

Compare Ledger and Statement.yxzp

All comments

geraldo

@anandakrishnan

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

XLOOKUP.yxmd

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

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!

Compare Ledger and Statement.yxzp

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703