[SCRIPTING] Calculated field from fields from two tables

[jumiprado]

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!!!!

[Digvijay_Singh]

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;

[jumiprado]

@

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!!

Digvijay‌

[Digvijay_Singh]

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.

[Digvijay_Singh]

If you can share sample data, I think I can verify the script shared.

[jonathandienst]

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.....;

[jonathandienst]

(Script attached)

[jumiprado]

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!

[Digvijay_Singh]

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.