Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
[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;
Hi Peter,
this works very well.
Thank you
Max