Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables one is of invoices of various customers and the second table of receipts of multiple customers. I want to adjust invoices against invoice in chronological order.
The invoice table of multiple customers is as follows
Customer | Customer Name | Invoice Date | Invoice Doc | Doc Type | Amount |
112999 | ABC | 26/09/2022 | 8232104556 | RV | 22892 |
112999 | ABC | 07/10/2022 | 8232104988 | RV | 27955 |
112999 | ABC | 20/10/2022 | 8232105436 | RV | 35123 |
112999 | ABC | 22/10/2022 | 8232105542 | RV | 16695 |
112999 | ABC | 31/10/2022 | 8232105661 | RV | 100170 |
112999 | ABC | 31/10/2022 | 8232105700 | RV | 11210 |
112999 | ABC | 04/11/2022 | 8232105793 | RV | 11682 |
104743 | XYZ | 20/09/2022 | 8232104353 | RV | 95390 |
104743 | XYZ | 20/09/2022 | 8232104354 | RV | 13381 |
104743 | XYZ | 29/09/2022 | 8232104827 | RV | 47553 |
104743 | XYZ | 30/09/2022 | 8232104831 | RV | 2381 |
104743 | XYZ | 10/10/2022 | 8232105078 | RV | 63107 |
104743 | XYZ | 14/10/2022 | 8232105267 | RV | 15777 |
104743 | XYZ | 19/10/2022 | 8232105417 | RV | 3216 |
104743 | XYZ | 04/11/2022 | 8232105801 | RV | 7144 |
104743 | XYZ | 26/11/2022 | 8232106325 | RV | 3302 |
104743 | XYZ | 26/11/2022 | 8232106326 | RV | 4321 |
104743 | XYZ | 07/12/2022 | 8232106510 | RV | 22832 |
104743 | XYZ | 27/12/2022 | 8232107001 | RV | 4842 |
104743 | XYZ | 27/12/2022 | 8232107002 | RV | 25674 |
104743 | XYZ | 11/01/2023 | 8220318106 | RV | 16057 |
104743 | XYZ | 31/01/2023 | 8220318497 | RV | 2421 |
104743 | XYZ | 31/01/2023 | 8220318498 | RV | 23235 |
104743 | XYZ | 10/02/2023 | 8220318653 | RV | 8508 |
The receipts table of multiple customers is as follows;
Customer | Customer Name | Receipt Date | Receipt Doc | Doc Type | Amount |
112999 | ABC | 29/10/2022 | 1400021259 | DZ | 86971 |
112999 | ABC | 31/10/2022 | 1600020219 | DG | 6031 |
112999 | ABC | 13/01/2023 | 1400000657 | DZ | 80000 |
104743 | XYZ | 19/09/2022 | 1400017107 | DZ | 133185 |
104743 | XYZ | 28/09/2022 | 8280407881 | YC | 781 |
104743 | XYZ | 29/09/2022 | 1400018132 | DZ | 200000 |
104743 | XYZ | 30/09/2022 | 1600017640 | DG | 2403 |
104743 | XYZ | 27/10/2022 | 8280408177 | YC | 1475 |
I want to prepare the new table and desired output table will be as follows;
Customer | New Code | Customer Name | Invoice Date | Invoice Doc | Doc Type | Amount | Receipt Date | Receipt Doc | Doc Type | Amount |
112999 | 10000368 | ABC | 26/09/2022 | 8232104556 | RV | 22892 | 29/10/2022 | 1400021259 | DZ | 22892.00 |
112999 | 10000368 | ABC | 07/10/2022 | 8232104988 | RV | 27955 | 29/10/2022 | 1400021259 | DZ | 27955.00 |
112999 | 10000368 | ABC | 20/10/2022 | 8232105436 | RV | 35123 | 29/10/2022 | 1400021259 | DZ | 35123.00 |
112999 | 10000368 | ABC | 22/10/2022 | 8232105542 | RV | 1001 | 29/10/2022 | 1400021259 | DZ | 1001.10 |
112999 | 10000368 | ABC | 22/10/2022 | 8232105542 | RV | 6031 | 31/10/2022 | 1600020219 | DG | 6031.00 |
112999 | 10000368 | ABC | 22/10/2022 | 8232105542 | RV | 9663 | 13/01/2023 | 1400000657 | DZ | 9662.90 |
112999 | 10000368 | ABC | 31/10/2022 | 8232105661 | RV | 70337 | 13/01/2023 | 1400000657 | DZ | 70337.10 |
112999 | 10000368 | ABC | 31/10/2022 | 8232105661 | RV | 29833 | ||||
112999 | 10000368 | ABC | 31/10/2022 | 8232105700 | RV | 11210 | ||||
112999 | 10000368 | ABC | 04/11/2022 | 8232105793 | RV | 11682 | ||||
104743 | 10007038 | XYZ | 20/09/2022 | 8232104353 | RV | 95390 | ||||
104743 | 10007038 | XYZ | 20/09/2022 | 8232104353 | RV | 95390 | 19/09/2022 | 1400017107 | DZ | 95390.00 |
104743 | 10007038 | XYZ | 20/09/2022 | 8232104354 | RV | 13381 | 19/09/2022 | 1400017107 | DZ | 13381.00 |
104743 | 10007038 | XYZ | 29/09/2022 | 8232104827 | RV | 24414 | 19/09/2022 | 1400017107 | DZ | 24413.99 |
104743 | 10007038 | XYZ | 29/09/2022 | 8232104827 | RV | 781 | 28/09/2022 | 8280407881 | YC | 781.00 |
104743 | 10007038 | XYZ | 29/09/2022 | 8232104827 | RV | 22358 | 29/09/2022 | 1400018132 | DZ | 22358.01 |
104743 | 10007038 | XYZ | 30/09/2022 | 8232104831 | RV | 2381 | 29/09/2022 | 1400018132 | DZ | 2381.00 |
104743 | 10007038 | XYZ | 10/10/2022 | 8232105078 | RV | 63107 | 29/09/2022 | 1400018132 | DZ | 63107.00 |
104743 | 10007038 | XYZ | 14/10/2022 | 8232105267 | RV | 15777 | 29/09/2022 | 1400018132 | DZ | 15777.00 |
104743 | 10007038 | XYZ | 19/10/2022 | 8232105417 | RV | 3216 | 29/09/2022 | 1400018132 | DZ | 3216.00 |
104743 | 10007038 | XYZ | 04/11/2022 | 8232105801 | RV | 7144 | 29/09/2022 | 1400018132 | DZ | 7144.00 |
104743 | 10007038 | XYZ | 26/11/2022 | 8232106325 | RV | 3302 | 29/09/2022 | 1400018132 | DZ | 3302.00 |
104743 | 10007038 | XYZ | 26/11/2022 | 8232106326 | RV | 4321 | 29/09/2022 | 1400018132 | DZ | 4321.00 |
104743 | 10007038 | XYZ | 07/12/2022 | 8232106510 | RV | 22832 | 29/09/2022 | 1400018132 | DZ | 22832.00 |
104743 | 10007038 | XYZ | 27/12/2022 | 8232107001 | RV | 4842 | 29/09/2022 | 1400018132 | DZ | 4842.00 |
104743 | 10007038 | XYZ | 27/12/2022 | 8232107002 | RV | 25674 | 29/09/2022 | 1400018132 | DZ | 25674.00 |
104743 | 10007038 | XYZ | 11/01/2023 | 8220318106 | RV | 16057 | 29/09/2022 | 1400018132 | DZ | 16057.00 |
104743 | 10007038 | XYZ | 31/01/2023 | 8220318497 | RV | 2421 | 29/09/2022 | 1400018132 | DZ | 2421.00 |
104743 | 10007038 | XYZ | 31/01/2023 | 8220318498 | RV | 6568 | 29/09/2022 | 1400018132 | DZ | 6567.99 |
104743 | 10007038 | XYZ | 31/01/2023 | 8220318498 | RV | 2403 | 30/09/2022 | 1600017640 | DG | 2403.00 |
104743 | 10007038 | XYZ | 31/01/2023 | 8220318498 | RV | 1475 | 27/10/2022 | 8280408177 | YC | 1475.00 |
104743 | 10007038 | XYZ | 31/01/2023 | 8220318498 | RV | 12789 | ||||
104743 | 10007038 | XYZ | 10/02/2023 | 8220318653 | RV | 8508 |
Your sample data does not match your description. Are you assuming that receipt values match invoice values? Is there an indication on the receipt which invoice it is for?
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.