Complement missing data with Excel-file

[Joel_Holmberg]

Hello,

I've tried to find a solution all day without any success, but I'm sure it's a walk in the park for most of you 🙂

I'm loading a .txt file (which I'm generating daily) containing transaction data.

The data is basically;

Supplier Name; Supplier ID; Cost

However - some of the entries in the transaction data is missing the supplier ID (but do got the name).

These are fairly regular and my aim is to complement this with an Excel-sheet containing the name and the ID (which is missing in the .txt file I automatically generate everyday).

So basically the Excel-file looks like this;

Supplier Name; Supplier ID.

What I'd like it to do is - if there's a supplier ID in the .txt file, use it. If there's not, use the ID from the Excel-file (and use the name to match).

The code to load the tables are as below;

Data:
LOAD
*
FROM [x.txt]
(txt, utf8, embedded labels, delimiter is '|', msq);

MISSING_SUPPLIER:
LOAD
SUPPLIER_NAME,
SUPPLIER_ID
FROM [x.xlsx]
(ooxml, embedded labels, table is [x]);

[Lauri]

Data1:
LOAD
SUPPLIER_NAME,
SUPPLIER_ID,
COST
FROM [x.txt]
(txt, utf8, embedded labels, delimiter is '|', msq);

LEFT JOIN LOAD
SUPPLIER_NAME,
SUPPLIER_ID as ID_if_missing
FROM [x.xlsx]
(ooxml, embedded labels, table is [x]);

Data:
LOAD
If(Len(SUPPLIER_ID)=0, ID_if_missing, SUPPLIER_ID) as SUPPLIER_ID,
SUPPLIER_NAME,
COST
Resident Data1;

Drop Table Data1;