Report with help of 2 tables and conditions

[Anonymous]

Hello All

I have 2 tables, OLD and NEW:

IN OLD DATA TABLE as below , where there are some BID's  and Staus is Confirm and certain amount .

In NEW DATA  TABLE for some of the BID's of Old table status becomes Cancelled and amount becomes 0.

For few the amount changes whereas status remains same.

I want a report as 3rd table --which is REQUIRED OUTPUT Table

If Status gets Cancelled then replace 0 by (AMOUNT of OLD Table) *-1  and  if status is confirmed then keep the AMOUNT of NEW DATA Table in the report .

Kindly help how this can be achieved.

OLD DATA TABLE

BID	DATE	STATUS	AMOUNT
BK123	20-02-2018	CONFIRM	10000
BK387	22-02-2018	CONFIRM	5000
BD255	25-02-2018	CONFIRM	4580
CH999	27-02-2018	CONFIRM	3636

NEW DATA TABLE

BID	DATE	STATUS	AMOUNT
BK123	15-03-2018	CANCELLED	0
BK387	22-02-2018	CONFIRM	5400
BD255	21-03-2018	CANCELLED	0
CH999	27-02-2018	CONFIRM	3636

REPORT -REQUIRED OUTPUT

BID	DATE	STATUS	AMOUNT
BK123	15-03-2018	CANCELLED	-10000
BK387	22-02-2018	CONFIRM	5400
BD255	21-03-2018	CANCELLED	-4580
CH999	27-02-2018	CONFIRM	3636

Thanks & Regards

Chintan Gala

[olivierrobin]

hello

i would JOIn the 2 table first

so, you have new and old values on the same row

and then make the calculations as desired to have the 3rd table

[Anonymous]

Dear Oliview

Thanks for your reply.

I have joined the tables. I am able to get Amount of OLD TABLE.

As soon as I use the if condition , STATUS is CANCELLED to get sum in Report , there is no data.

Regards

Chintan Gala

[olivierrobin]

could you post your code ?

[Anonymous]

Hello Olivier

Thanks for your help. I am able to get the result by joining the tables and using Flags in each table.