Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Billing |
---|
ClientID |
Bill_ID |
Period |
Amount |
Bill_Type |
Date_Billing |
I give an example for BILLING table:
ClientID | Bill_ID | Period | Amount | Bill_Type | BILL_CANCEL | Date_Billing |
---|---|---|---|---|---|---|
Client_001 | BILL_0001 | may-15 | 100 | Bill | 0 | 01/05/2015 |
Client_002 | BILL_5030 | Apr-2015 | 40 | Bill | 0 | 04/04/2015 |
Client_002 | Bill_5043 | Apr-2015 | -20 | Credit Note | BILL_5030 | 06/04/2015 |
Client_003 | BILL_6074 | jun-15 | 50 | Bill | 0 | 07/06/2015 |
Client_001 | BILL_0001 | jul-15 | -30 | Credit Note | BILL_0001 | 05/07/2015 |
Collection |
---|
ClientID |
PaymentID |
Period |
Amount |
Payment_Type |
Date_Collection |
ClientID | PaymentID | Period | Amount | Payment_Type | Date_Collection |
---|---|---|---|---|---|
Client_001 | P_3059 | May-2015 | 80 | Payment | 04-05-2015 |
Client_002 | P_5492 | Apr-2015 | 20 | Payment | 07-04-2015 |
Client_003 | P_8765 | Jun-2015 | 50 | Payment | 14-06-2015 |
I Need to create a new table similar to this:
ClientID | Bill_ID | Period | Amount | BILL_CANCEL | PERIOD_CANCEL | Date_Billing |
---|---|---|---|---|---|---|
Client_001 | BILL_0001 | may-15 | 100 | 0 | 0 | 01/05/2015 |
Client_002 | BILL_5030 | Apr-2015 | -20 | 0 | 0 | 04/04/2015 |
Client_002 | Bill_5043 | Apr-2015 | -20 | BILL_5030 | Apr-2015 | 06/04/2015 |
Client_003 | BILL_6074 | jun-15 | 50 | 0 | 0 | 07/06/2015 |
Client_001 | BILL_0899 | jul-15 | -30 | BILL_0001 | may-15 | 05/07/2015 |
Thanks youu!
In your script, create a Bill_Cancel table with some code like this:
BILL_Cancel:
LOAD BILL_ID AS BILL_CANCEL,
Period AS PERIOD_CANCEL
RESIDENT Billing
WHERE BILL_Type = 'Credit Note';
Reload and add field PERIOD_CANCEL to your main dashboard object. Associative logic will perform its magic.
If you need this PERIOD_CANCEL field as an extra column in the internal Billing table itself, convert BILL_Cancel into a Mapping table, transfer Billing into a second resident table and at the same time use
:
applymap('BILL_Cancel', BILL_CANCEL, 0) AS PERIOD_CANCEL,
:
Best,
Peter