topic Re: Link to file by different fields in QlikView

Link to file by different fields

Tue, 01 Sep 2015 15:18:02 GMT

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

Re: Link to file by different fields

Peter_Cammaert — Tue, 01 Sep 2015 16:43:42 GMT

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

Re: Link to file by different fields

maxgro — Wed, 02 Sep 2015 19:50:19 GMT

[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;

Re: Link to file by different fields

Thu, 03 Sep 2015 15:10:46 GMT

Hi Peter,

this works very well.

Thank you

Max