Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Guys! I need to make differents calculated fields in the script@ from two tables (Billing and Collection)
Billing |
---|
ClientID |
Bill_ID |
Period |
Amount |
Bill_Type |
Date_Billing |
I give an example for BILLING table:
ClientID | Bill_ID | Period | Amount | Bill_Type | Date_Billing |
---|---|---|---|---|---|
Client_001 | BILL_0001 | May-2015 | 100 | Bill | 01-05-2015 |
Client_002 | BILL_5030 | Apr-2015 | 40 | Bill | 04-04-2015 |
Client_002 | Bill_5043 | Apr-2015 | -20 | Credit Note | 06-04-2015 |
Client_003 | BILL_6074 | Jun-2015 | 50 | Bill | 07-06-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 make fields with differents calculations Like this:
Calculation_Fields_Table |
---|
ClientID |
Amount (From Billing) - Amount (from Collection) = DEBT_FIELD |
Date_Billing - Date_Collection = #_of_Days |
If(Bill_Type="Credit Note", 1) as #_Of_CreditNotes_Billing |
If(Payment_Type="Credit Note", 1) as #_Of_CreditNotes_Collection |
I would like to create, if it is possible, a table like this:
ClientID | DEBT_FIELD | #_of_Days | #_Of_CreditNotes_Billing | #_Of_CreditNotes_Collection |
---|---|---|---|---|
Client_001 | 20 | 3 (days) | 0 | 0 |
Client_002 | 0 | 3 (days) | 1 | 0 |
Client_003 | 0 | 7 (days) | 0 | 0 |
I think it's better to relate the tables by the ClientID field more than concatenate because i have a table Clients, but would like to help me solve this problem in the best possible option.
The table CLIENT is similar to this:
Clients |
---|
ClientID |
Client_Name |
Client_Age |
Client_Birthday |
Thank you all for this help!!!!
Try like this, share sample data if you find any trouble in using it -
Billing:
Load ClientID,
Bill_ID,
Period,
Amount as Bill_Amount,
Bill_Type,
Date_Billing
From <Your Billing file>;
inner join(Billing)
Collection:
Load ClientID,
PaymentID,
Period as Collection_Period,
Amount as Coll_Amount,
Payment_Type,
Date_Collection
From <Your Collection File>;
Calculation_Field_Table:
Load *,
#_Of_CreditNotes_Billing - #_Of_CreditNotes_Collection as #_of_CreditNotes;
Load ClientID,
Bill_Amount-Coll_Amount as DEBT_FIELD,
Date_Billing-Date_Collection as [# of days],
If(Bill_Type='Credit Note',1) as #_Of_CreditNotes_Billing,
If(Payment_Type='Credit Note',1) as #_Of_CreditNotes_Collection
Resident Billing;
@
Digvijay Singh do you think that INNER JOIN is better than relate the tables ? I tried something like this but with a LEFT JOIN, but when i make a simple table in qlikview the total is wrong.
If I have a problem i am going to tell here!
Thank You a lot!!
I think inner join is must here because if you use left join you may have records with only billing information if there is no collection record with same client_Id.
Using Inner join we will have only records having both billing and collection information because your calculation table requirement makes sense only when you have both billing and collection data for one client_id.
Also you may need to ensure your dates are in proper date format else you won't get no of days right, use Date(Date# functions to format them properly.
If you can share sample data, I think I can verify the script shared.
I would almost certainly concatenate the billings and collections into a single table. It will greatly simplify your front end analysis and avoid the need for links tables and/or multiple calendars as well as probable improvement in load and front end performance:
Transactions:
LOAD
ClientID
Bill_ID As DocID
Period
Amount
Bill_Type As TransType
Date_Billing As TransDate
'Billing' As Source
FROM Billing......;
Concatenate (Transactions)
LOAD
ClientID
PaymentID As DocID
Period
Amount
Payment_Type As TransType
Date_Collection As TransDate
'Payments' As Source
FROM Payments.......;
Clients:
LOAD
ClientID
Client_Name
Client_Age
Client_Birthday
FROM Clients.....;
(Script attached)
The problem is that sometimes i dont have values for fields in Collection table, because the client didnt pay the bill, do you think that INNER JOIN is the way?
THANKS!
I suggest trying concatenation as suggested by jontydkpi which may help in handling multiple scenarios existing in your data.
Inner join will exclude billing client_id if you don't have corresponding collection_id. it will only filter records with both billing and collection record existing for a specific client_ID. You may go for left join as I suppose you would always want to keep billing record in final joined table.As I know you cannot have collection record without any billing record so left join is fine for that. But I prefer trying on good sample as there could be other scenarios like more than one collection record for one billing record. Left join will create multiple records for one client ID and multiple collection record so we may need group by Client_ID to get integrated information for one client Id.
Please try or share sample data to verify the solutions provided.