how to get association between three tables

[Gopi_E]

Hi Qlikers,

@sunny_talwar could you please look in to this

i have a requirement like,  i have a three tables from this table i need to calculate 3 metrics, 

Route_Name	Route_Tolls	Route_Distance
CPT-DBN	5	1650
JHB-DBN	4	1400
CPT-JHB	5	570
POL-JHB	4	320

Gantry	Gantry_Name	Route	Latitude	Longitude
TG001	BARBET	N1	-228739	289980
TG002	MOSSIE	N1	-238983	289389
TG003	INDIALIA	N1	-234646	280548
TG004	PIKOKO	N1	-230908	280566

Reg	Toll_Id	Toll_Name	Toll_Type	TransDate	Trans_Time	Terminal	Amount
BP64FB	TG05	05 IVUSI	Etoll_Gantry	07-10-2019	13:19	1005	1500
BP64FB	TG06	6 IVUSI	Etoll_Gantry	08-10-2019	14:19	1006	1501
BP64FB	TG07	7 IVUSI	Etoll_Gantry	09-10-2019	15:19	1007	1502
BP64FB	TG05	8 IVUSI	Etoll_Gantry	10-10-2019	16:19	1008	1503

from above table i need to calculate 

1. Number of times a trip/Route has been driven

2.Total cost of the trips

3. Using the provided route distance calculate CPK for each trip/route

[ramasaisaksoft]

Hi

use AutoNumber() for each table so that it will create a new filed with same name

now create this auto number for 3 tables and give the same name.so that association will create between tables automatically.

Now u can do your calculations/Expressions.

[Gopi_E]

Hi ramasai,

thanks for the reply,

for Autonumber() which fields i have to use for generating new field.

[sunny_talwar]

What would be the required output based on the sample data provided?

[Brett_Bleess]

Gopi, just making sure you saw Sunny's post...  He requested additional info on what your output needs to look like in comparison to the inputs, please be sure to circle back and provide an update with that info if you still need help.  If you resolved things, consider posting what you did, and then use the Accept as Solution button on that post afterward to mark it as the solution.

Regards,
Brett

[Gopi_E]

Sorry for the late reply sunny, I was on leave

Here requirement is How can i associate above three tables, which field i need to use for getting association.

Please give any suggestions.

[Kushal_Chawda]

Looking at your data I don't see any column on which you can associate the 3 tables. But the same question you can ask to your business or database owner. Or you can ask a logic to calculate KPIs

[Gopi_E]

Hi kush,

thanks for the reply,

do you have any idea how i can calculate Number of times  trips driven 

[Kushal_Chawda · Accepted solution]

as per the data, it should be count(distinct Route_Name) but as I said ask this question to your business and clarify

[Gopi_E]

Thank you,