topic Multiple joins to same table in QlikView

Multiple joins to same table

Thu, 31 May 2012 03:49:56 GMT

Hi All

I have a table called Inventory as below:

Inventory ID

Source Location ID

Destination Location ID

which needs to join to a Locations table:

Location ID

Location Name

Both tables are loaded via a QVD.

Ideally the "Source Location ID" and "Destination Location ID" fields would automatically join to "Location ID" but this doesn't happen because the column names are different. I'd have to rename "Source Location ID" and "Destination Location ID" to "Location ID" which I obviously can't do because you cannot have 2 fields with the same name on a single table.

So my question - Is there any way to force a join in the model between 2 tables where the fields are not named alike?

The only way I can see to make this happen is to load the Locations.qvd twice as "Source Locations" with a "Source Location ID" and "Destination Locations" with a "Destination Location ID". This way each field would join to a separate table that contains the same data rather than a single table. This seems quite inefficient to me so was wondering if there was a better way.

Thanks

Gregg.

Multiple joins to same table

Thu, 31 May 2012 04:06:17 GMT

You can force join by Concatenate Load.

Multiple joins to same table

Thu, 31 May 2012 04:18:57 GMT

Thanks Max. So your suggesting I'd join to the Locations table when loading the Inventory table and bring in the Location Name to the Inventory table? I'm new to Qlikview so any chance you can give me an example?

Thanks

Gregg.

Multiple joins to same table

Anonymous — Thu, 31 May 2012 05:14:55 GMT

Hi,

Try this by using ApplyMap:

Locations:

MAPPING

LOAD * INLINE [

Location ID, Location Name

1, AA

2, BB

3, CC

4, DD

5, EE

6, FF

7, GG

8, HH

9, JJ

];

Inventory:

LOAD

APPLYMAP('Locations', "Source Location ID") AS "Source Location Name",

APPLYMAP('Locations', "Destination Location ID") AS "Destination Location Name";

LOAD * INLINE [

Inventory ID, Source Location ID, Destination Location ID

A1, 1, 4

B1, 4, 9

C1, 7, 3

D1, 2, 5

];

Thx..

Multiple joins to same table

Thu, 31 May 2012 05:29:21 GMT

I think you can should try mapping load. I have created an excel sheet as datasource to demonstrate it

Table data in sheet are mentioned below.

Inventory:

Inventory ID Source Location ID Destination Location ID

1 5 5

2 3 2

3 2 3

4 4 3

5 4 3

Locations:

Location ID Location Name

1 Pakistan

2 India

3 Japan

4 New York

5 Jordan

6 Nepal

7 Bangladesh

8 China

9 London

10 Bahrain

I have loaded script as mentioned below, after loading the data as mentioned below create a table box and add list box for Location Id and Name and then check it.

LocationMap:

Mapping LOAD [Location ID],

[Location Name]

FROM

(ooxml, embedded labels, table is Locations);

Map [Source Location ID], [Destination Location ID] using LocationMap;

Inventory:

LOAD [Inventory ID],

[Source Location ID],

[Destination Location ID]

FROM

(ooxml, embedded labels, table is Inventory);

If it does not meet your requirements then let me know. May be i can suggest any other solution.

Kind Regards,

Adnan Sultan

Multiple joins to same table

jagan — Thu, 31 May 2012 06:38:29 GMT

Hi,

Try this way

Inventory:

LOAD

InventoryID,

SourceLocationID,

DestinationLocationID

FROM DataSource;

LEFT JOIN(Inventory)

LOAD

LocationID AS SourceLocationID,

LocationName AS SourceLocation

FROM DataSource1;

LEFT JOIN(Inventory)

LOAD

LocationID AS DestinationLocationID,

LocationName AS DestinationLocation

FROM DataSource1;

So that now source and destination location names are in the same table. If you want to maintain in individual table just remove left join.

Hope this helps you.

Regards,

Jagan.

Multiple joins to same table

Fri, 01 Jun 2012 00:50:11 GMT

Hi All

Thanks for the help, really appreciated. Both the ApplyMap and Join solutions work. With the ApplyMap it needed single quotes whereas your solution has double quotes so I had to modify slightly to get it to work (including in case someone else uses the solution). I've gone with the ApplyMap solution for now.

Thanks again

Gregg.

Multiple joins to same table

Fri, 01 Jun 2012 00:52:10 GMT

Oh one thing I forgot to mention that I find strange is that once you mark a table as a "Mapping" table it no longer shows up in the Table Viewer. Anyone know why this happens?

Thanks

Gregg.

Multiple joins to same table

Fri, 01 Jun 2012 01:31:00 GMT

Mapping tables stored in memory and dropped automatically after script execution. That's a beauty of it, that you don't need to remember to drop.