Getting a value from another table in a load if then else statement

[bigmike7]

Does anyone know how to pull a value from another table while doing a left join?

I am trying to pull best address based on what value is populated if we get it from the file in the left join use that if not use the default.

thank you,

Demographic:

Directory;

LOAD @1 as REL_ID,

     @2 as PROVIDER_IDENTIFICATION,

     @3 as Data_Agent_Code,

     @4 as Writer_Type,

     @5 as [FIRST NAME],

     @6 as [MIDDLE NAME],

     @7 as [LAST NAME],

     @8 as TITLE,

     @9 as SUFFIX,

     @10 as [SPECIALTY CODE],

     @11 as [SPECIALTY DESCRIPTION],

     @12 as ADDRESS,

FROM

(txt, codepage is 1252, no labels, delimiter is '|', msq);

LEFT JOIN ([Demographic])

LOAD

  [Best SHA ID] as REL_ID,

     [Veeva Address],

     [Veeva City],

     [Veeva State],

     [Veeva Zip],

     if([Veeva Address] <> '',[Veeva Address], ADDRESS) as [Final Address]

[MarcoWedel]

please supply sample data and expected result

[bigmike7]

it would be something like :

- Load Demographic file 1 that has address a

- load subset demographic file 2 with better address

- Left Join file 2 into file 1 if address exists in file 2 use file 2 address in column final address if not use original address from file 1

[bigmike7]

I am having trouble since I am trying to refer to a value from the first load not the join

[maxgro]

first join the 2 tables in a temporary table

then read the temporary table and create new field [Final Address]

drop the temporary table

Directory;

TmpDemographic:

LOAD @1 as REL_ID,

     @2 as PROVIDER_IDENTIFICATION,

     @3 as Data_Agent_Code,

     @4 as Writer_Type,

     @5 as [FIRST NAME],

     @6 as [MIDDLE NAME],

     @7 as [LAST NAME],

     @8 as TITLE,

     @9 as SUFFIX,

     @10 as [SPECIALTY CODE],

     @11 as [SPECIALTY DESCRIPTION],

     @12 as ADDRESS

FROM

(txt, codepage is 1252, no labels, delimiter is '|', msq);

LEFT JOIN ([TmpDemographic])

LOAD

    [Best SHA ID] as REL_ID,

     [Veeva Address],

     [Veeva City],

     [Veeva State],

     [Veeva Zip]

FROM

     ??????;

Demographic

NOCONCATENATE

LOAD

     *,

     if([Veeva Address] <> '',[Veeva Address], ADDRESS) as [Final Address]

resident

     TmpDemographic;

drop table TmpDemographic;

[bigmike7]

Thank you sir

Michael F. Henrick

Associate Director Sales Operations

mhenrick@medacpharma.com

Office: 312-854-0512

Mobile: 847-778-6356