Solved: Enrich existing column Load script - Qlik Community

CasperQlik

Hi Qlik Community

I need to enrich a column, lets call it value.

"Value" has a corresponding ID column.

In my main table I have a table of IDs and values but some values are blank.

In a second table I have the same columns but values that are missing from the main table.

How do I enrich my main table with the missing values from the second table?

//Casper

TauseefKhan

To enrich your main table with the missing values from the second table in Qlik Sense, you can use the Left Join or Map functions in the load script. Here's how you can do it:

##################### Option 1: Using Left Join #####################

// Load your main table
MainTable:
LOAD ID,
Value
FROM [YourMainTableSource];

// Load your second table
SecondTable:
LOAD ID,
Value as SecondValue
FROM [YourSecondTableSource];

// Perform the left join to enrich the main table
LEFT JOIN (MainTable)
LOAD ID,
SecondValue
RESIDENT SecondTable
WHERE NOT IsNull(SecondValue);

// Update the null values in the main table with the values from the second table
LOAD ID,
If(IsNull(Value), SecondValue, Value) as Value
RESIDENT MainTable;

##################### Option 2: Using Mapping ###################

// Load your second table as a mapping table
ValueMap:
MAPPING LOAD ID,
Value
FROM [YourSecondTableSource];

// Load your main table and apply the mapping
MainTable:
LOAD ID,
ApplyMap('ValueMap', ID, Value) as Value // This will replace null values in 'Value' with corresponding values from the second table
FROM [YourMainTableSource];

In both options, replace [YourMainTableSource] and [YourSecondTableSource] with the actual paths to your data sources. The LEFT JOIN method physically joins the tables, while the MAPPING method is more memory efficient and doesn't alter the structure of your main table.

Remember to drop the SecondTable after the join if you use the first option to avoid having redundant data in your data model:

DROP TABLE SecondTable;

Choose the method that best fits your needs and data structure.

** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. **

View solution in original post

TauseefKhan