topic Re: [SCRIPTING] Calculated field from fields from two tables in QlikView

[SCRIPTING] Calculated field from fields from two tables

jumiprado — Fri, 02 Oct 2015 03:12:01 GMT

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
Client_001	20	3 (days)	0
Client_002	0	3 (days)	1
Client_003	0	7 (days)	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!!!!

Re: [SCRIPTING] Calculated field from fields from two tables

Digvijay_Singh — Fri, 02 Oct 2015 03:28:53 GMT

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;

Re: [SCRIPTING] Calculated field from fields from two tables

jumiprado — Fri, 02 Oct 2015 03:38:37 GMT

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‌

Re: [SCRIPTING] Calculated field from fields from two tables

Digvijay_Singh — Fri, 02 Oct 2015 04:13:21 GMT

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.

Re: [SCRIPTING] Calculated field from fields from two tables

Digvijay_Singh — Fri, 02 Oct 2015 04:14:27 GMT

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

Re: [SCRIPTING] Calculated field from fields from two tables

jonathandienst — Fri, 02 Oct 2015 05:26:29 GMT

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

Re: [SCRIPTING] Calculated field from fields from two tables

jonathandienst — Fri, 02 Oct 2015 05:28:37 GMT

(Script attached)

Re: [SCRIPTING] Calculated field from fields from two tables

jumiprado — Fri, 02 Oct 2015 11:38:24 GMT

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!

Re: [SCRIPTING] Calculated field from fields from two tables

Digvijay_Singh — Fri, 02 Oct 2015 17:26:42 GMT

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.