Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You can force join by Concatenate Load.
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.
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..
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
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.
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.
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.
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.