Multiple joins to same table

[#1]

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.

[#2]

You can force join by Concatenate Load.

[#3]

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.

[Anonymous]

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

[#5]

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

[jagan]

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.

[#7]

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.

[#8]

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.

[#9]

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.