Add new column with values from reference table

[jan2]

Hi,

I loaded a table with my main data and a reference table. Now I want to create a new column in the main data with a coresponding value from the reference table.

Simple example:

Main data contains the column Location. The reference table contains every location one time and a location_id for every location.

Now I need a new column in the main data which shows the right location_id for every location.

[GaryGiles]

After loading the Main and Reference_Table, the basic structure for adding a column to Main would look like this:

Left Join (Main)
Load Location,
           location_id
Resident Reference_Table;

Note:  If you are going to leave the Reference_Table in the model, you will want to rename the location_id in the Main table to something distinct.

[jan2]

I get the mistake that the value "Location" is not found

[jan2]

At the moment the syntax is like:

[Main]:

Load 

Location,

Data X,

Data Y

From[folder]

[Reference]

Load

Location(Other name then in main table),

Location_ID,

Data Z

From[folder]

Left join[Main]

Load Location,

           Location_ID

Resident Reference;

[GaryGiles]

Try this:

[Main]:

Load 

Location,

Data X,

Data Y

From[folder]

[Reference]

Load

Location,

Location_ID,

Data Z

From[folder]

Left join[Main]

Load Location,

           Location_ID as Main.Location_ID

Resident Reference;

[Rodj]

As described in the online help Using a left join isn't terribly efficient and is not the recommended way of doing this. Instead, if you are just looking to add a single column from your reference table you should use a mapping table, as described in the help here. You'll also find this discussed in numerous posts here in Community.

It would look something like this:

Map_Location:
Mapping Load
Location,
Location_id
FROM [lib://mylib/ReferenceLocations.csv]
(csv);

Main:
Load
Field1,
Field2,
ApplyMap('Map_Location', Location, 'No location data exists') as Location_id
from [lib://mylib/filename.csv]
(csv);

You don't need to manage the mapping table as once the load is complete Qlik will simply discard it. This way you don't need to keep the reference data in your app beyond what is needed.

[jan2]

In the Map_Location: it can't find the Location, probably because it is not in the Reference Location  csv but in the main data csv