topic Re: Getting a value from another table in a load if then else statement in QlikView

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

bigmike7 — Sun, 07 Sep 2014 17:03:41 GMT

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]

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

MarcoWedel — Sun, 07 Sep 2014 17:07:54 GMT

please supply sample data and expected result

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

bigmike7 — Sun, 07 Sep 2014 17:15:42 GMT

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

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

bigmike7 — Sun, 07 Sep 2014 17:16:40 GMT

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

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

maxgro — Sun, 07 Sep 2014 17:19:42 GMT

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;

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

bigmike7 — Sun, 07 Sep 2014 17:23:03 GMT

Thank you sir

Michael F. Henrick

Associate Director Sales Operations

mhenrick@medacpharma.com

Office: 312-854-0512

Mobile: 847-778-6356