Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
please supply sample data and expected result
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
I am having trouble since I am trying to refer to a value from the first load not the join
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;
Thank you sir
Michael F. Henrick
Associate Director Sales Operations
mhenrick@medacpharma.com
Office: 312-854-0512
Mobile: 847-778-6356