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.
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.
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.
