Link to file by different fields

[#1]

Hi All,

I have two Excel files: File A and File B.

File A has the a column which contains the direct customer code (named Dir_Cust) and a second column which contains the final customer code (named Fin_Cust). The data into the second column are not always available because when final customer and direct customer correspond the second field is kept blank.

The file B contains in the first column (named Customer_code)  the list of all customer code (both direct and final) and in the second column (named Customer_address) their address.

My need is to link these two table to have always the data for final customer, with the following instruction:

If Fin_Cust field is blank then link Dir_Cust with Customer_code otherwise link Fin_Cust with Customer_code.

Do you have any idea?

I am not very skilled yet on QV so please keep it simple.

Furthermore I cannot manipulate these Excel files.

Thank you in advance

[Peter_Cammaert · Accepted solution]

Is it useful to keep both Customer codes from File A? If not, then load File A with only a Final customer code. Add a test to use the value in Dir_Cust if Fin_Cust proves to be empty. Something like:

[File A]:

LOAD

:

if (len(trim(Fin_Cust)) = 0, Dir_Cust, Fin_Cust) AS Fin_Cust,

:

The linking will now be only to field Fin_Cust. You can keep Dir_Cust for reference if you want, but it won't link to the address table.

Best,

Peter

[maxgro]

[File A]:

LOAD

    Dir_Cust,

    if (len(trim(Fin_Cust)) = 0, Dir_Cust, Fin_Cust) AS Fin_Cust,

    ...

from excela;

// add address to Dir_Cust

left join ([File A])

LOAD

    Customer_code          as Dir_Cust,

    Customer_address    as Dir_Cust_Address

from excelb;

// add address to Fin_Cust   

left join ([File A])

LOAD

    Customer_code          as Fin_Cust,

    Customer_address    as Fin_Cust_Address

from excelb;

[#4]

Hi Peter,

this works very well.

Thank you

Max