topic Re: how to get association between three tables in QlikView

how to get association between three tables

Gopi_E — Sat, 16 Nov 2024 01:46:39 GMT

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

Re: how to get association between three tables

ramasaisaksoft — Thu, 05 Dec 2019 06:19:05 GMT

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.

Re: how to get association between three tables

Gopi_E — Thu, 05 Dec 2019 06:43:30 GMT

Hi ramasai,

thanks for the reply,

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

Re: how to get association between three tables

sunny_talwar — Thu, 05 Dec 2019 18:11:03 GMT

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

Re: how to get association between three tables

Brett_Bleess — Mon, 16 Dec 2019 20:14:26 GMT

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

Re: how to get association between three tables

Gopi_E — Wed, 18 Dec 2019 11:30:50 GMT

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.

Re: how to get association between three tables

Kushal_Chawda — Wed, 18 Dec 2019 11:44:58 GMT

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

Re: how to get association between three tables

Gopi_E — Wed, 18 Dec 2019 11:57:01 GMT

Hi kush,

thanks for the reply,

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

Re: how to get association between three tables

Kushal_Chawda — Wed, 18 Dec 2019 11:59:51 GMT

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

Re: how to get association between three tables

Gopi_E — Wed, 18 Dec 2019 12:07:06 GMT

Thank you,