topic How to link two fields from one table to another ? in QlikView

How to link two fields from one table to another ?

Thu, 01 Sep 2011 19:28:15 GMT

Hello,

I have a table of Location, with the Fields ID_Location ,Info_Location and so do on.

Then I have another table, about routes, with the fields ID_Origin_Location, ID_Destination_Location, and Distance.

The problem is, I need to get the info of the location using the IDs, of the Origin Location and the Destination Location,

but if I use the same names for the ID fields in the load script, Qlikview complains about cyclic reference.

How can I solve that ?? Any help is much appreciated !!

Thanks !!

Josué

How to link two fields from one table to another ?

ToniKautto — Thu, 01 Sep 2011 20:13:14 GMT

I would suggest that you consider the possibility to join the two tables into one table.If that is not a good option for you then just add a keyfiled to the tables and rename the identical field on at least one of the table to avoid the association. The principle below should be applicable.

T1:

LOAD

F1 & F2 as KeyField,

F1 as T1.F1,

F2 as T1.F2,

FROM file.qvd;

T2:

LOAD

F1 & F2 as KeyField,

F1 as T2.F1,

F2 as T2.F2,

FROM anotherfile.qvd;

Re: How to link two fields from one table to another ?

johnw — Fri, 02 Sep 2011 01:07:17 GMT

I'm not sure I understood. Let's say you currently have this structure and data:

ID_Origin_Location, ID_Destination_Location, Distance
1, 2, 5

ID_Location, Info_Location
1, Bob's House
2, Carla's House

One thing you could do is duplicate the location table:

ID_Origin_Location, ID_Destination_Location, Distance
1, 2, 5

ID_Origin_Location, Info_Origin_Location
1, Bob's House

2, Carla's House

ID_Destination_Location, Info_Destination_Location
1, Bob's House

2, Carla's House

A different thing you could do is convert location columns into location rows on a new table:

ID_Route, Distance
1, 5

ID_Route, Location_Type, ID_Location
1, Origin, 1
1, Destination, 2

ID_Location, Info_Location
1, Bob's House
2, Carla's House

Both approaches are useful in different ways. It just depends on how you want to interact with your data.

Re: How to link two fields from one table to another ?

Fri, 02 Sep 2011 11:31:53 GMT

Hello John,

I think that the first approach is not good, because of the huge number of records I have.

I'll try the second one here. I'll let you know if it worked for me.

Thank you for all your help, people !!

Josué

Re: How to link two fields from one table to another ?

johnw — Fri, 02 Sep 2011 16:34:08 GMT

I wouldn't worry about the huge number of records in the first method, except possibly in terms of load time. QlikView's compression should in theory mean that the cloned table takes almost no memory at all. As far at the load goes, I'd hope that your table of locations loads from a QVD. Do both loads from the QVD using an optimized load, and the added time should be rather small, even if you have ten million locations you're managing. The below should give you optimized loads. I've also reduced the tables to only the required rows, which might help a little with load time and memory, and is a good idea regardless so that your list boxes don't advertise origins or destinations that don't exist.

Origins:
LOAD
ID_Location as ID_Origin_Location
,Info_Location as Info_Origin_Location
FROM Location.qvd (QVD)
WHERE exists(ID_Origin_Location,ID_Location)
;
Destinations:
LOAD
ID_Location as ID_Destination_Location
,Info_Location as Info_Destination_Location
FROM Location.qvd (QVD)
WHERE exists(ID_Destination_Location,ID_Location)
;

As I indicated, what method you use is more a matter of how you want to interact with your data. Expanding on that a little, with the first method, you can easily pose a query like "Show me all data associated with the route that goes from Bob's House to Carla's House". You can't do that easily with the second method. But in the second method, you can easily pose a query like "Show me all data associated with Bob's House, regardless of whether the route starts or ends there". You can't do that easily with the first method. Combining both allows you to pose both queries easily, but having three different location fields could get confusing for your users. I've generally used one or the other approach, not both, but I HAVE done both in the same application before (where my users are fairly sophisticated data analysts already).

How to link two fields from one table to another ?

Tue, 06 Sep 2011 19:05:33 GMT

Hello John,

I've tried both solutions, but the problem is, I loose the connection with other tables.

For example, one of the tables is about Products, with the following fields:

ID_Product, Info_Product, ID_Location

And another table about Product transformation, with the fields:

IDTransf, ID_Product_Input, ID_Product_Output, Input_amount, Output_amount

There are many Products as Input for the same Product as output, and many products for the same location.

I need to have a table showing the input products, output product and the Location of the Input and of the Output.

How can I do that ?

Thank you very much for all your help,

Josué

How to link two fields from one table to another ?

Jason_Michaelides — Tue, 06 Sep 2011 19:24:26 GMT

Hi Josue,

If you post your app here it will help us to see your situation more clearly.

Cheers,

Jason

Re: How to link two fields from one table to another ?

Tue, 06 Sep 2011 19:44:39 GMT

Hello Jason,

This table is what I need to do.

Thank you,

Josué

Re: How to link two fields from one table to another ?

Tue, 06 Sep 2011 19:59:15 GMT

I've removed the Locations of the table.

If I solve the problem with the input/output products, I think I can solve the other things.

Re: How to link two fields from one table to another ?

johnw — Wed, 07 Sep 2011 00:35:14 GMT

Why is the ID_Location on the Product table? Does the Info_Product differ by location?