Add value to table if string match from ohter table

[CehKay]

Hello everyone,

I have one table in an excel file (C:\cat.xlsx) with categories which looks like this

Categorie, Text
Cat1,Testtext1
Cat2,Testtext2
Cat3,Testtext3

Than i have a main CSV file (C:\main.csv) which has some columns, and if the value in ColumnX is part of any value of the "Text" column than add the according "Categorie" instead.

Pseudocode look like this:

Load Col1, Col2, Col3, ColumnX,

if ("ColumnX" part of any of "Text" from Categories) THEN add CatX as Main_Cat

ELSE add OTHER as Main_Cat

For example:

Excel File looks like this

Col1,Col2,Col3,ColumnX

A,B,C,text1
D,E,F,text2
G,H,I,nothing

In Qlik it should than look like this:

Col1,Col2,Col3,ColumnX,Main_Cat

A,B,C,text1,Cat1
D,E,F,text2,Cat2
G,H,I,nothing,OTHER

How can i do that in the LOAD statement?

[GaryGiles · Accepted solution]

Try the script below (replacing the initial inline loads with your Excel file loads).  There might be a way to reduce the steps if you work directly in the main Data table, but I wanted to isolate the process assigning Categorie to Text before adding it to the main data.

Categorie:
Load * inline [
Categorie, Text
Cat1,Testtext1
Cat2,Testtext2
Cat3,Testtext3
];

Data:
Load * inline [
Col1,Col2,Col3,ColumnX
A,B,C,text1
D,E,F,text2
G,H,I,nothing
];

//Load unique ColumnX values in TempTable
TempTable:
Load distinct ColumnX
Resident Data;

//Create a row for each Text value / Categorie value combination
Join (TempTable)
Load Text,
Categorie
Resident Categorie;

//Limit rows to only rows where ColumnX is contained in Text
TempTable2:
Load Distinct ColumnX,
Categorie
Resident TempTable
where wildmatch(Text,'*'&ColumnX&'*');

drop table TempTable;

//Reload TempTable with All unique ColumnX values
TempTable:
Load distinct ColumnX
Resident Data;

//Add Categorie derived in TempTable2 to TempTable
Join (TempTable)
Load ColumnX,
Categorie as CategorieTemp
Resident TempTable2;

//Replace Null values with 'Other'
Join (TempTable)
Load ColumnX,
if(IsNull(CategorieTemp),'Other',CategorieTemp) as Categorie
Resident TempTable;

//Join Catagorie to Data table
Join (Data)
Load ColumnX,
Categorie
Resident TempTable;

drop table TempTable, TempTable2, Categorie;