topic Re: Add new column with values from reference table in App Development

Add new column with values from reference table

jan2 — Sat, 16 Nov 2024 17:33:42 GMT

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.

Re: Add new column with values from reference table

GaryGiles — Thu, 28 Jan 2021 10:46:07 GMT

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.

Re: Add new column with values from reference table

jan2 — Thu, 28 Jan 2021 13:23:07 GMT

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

Re: Add new column with values from reference table

jan2 — Thu, 28 Jan 2021 13:36:35 GMT

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;

Re: Add new column with values from reference table

GaryGiles — Thu, 28 Jan 2021 23:54:44 GMT

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;

Re: Add new column with values from reference table

Rodj — Fri, 29 Jan 2021 02:17:56 GMT

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.

Re: Add new column with values from reference table

jan2 — Fri, 29 Jan 2021 10:36:50 GMT

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