where to put an if statement which includes 2 fields, loaded from different tables ?

[Anonymous]

I am trying to add an IF statement into my script, which created an YES,NO indicator field based on the value in 2 fields.

The complexity is that these fields are coming from 2 different tables. As you can see below [Calc. AO] comes from the territories.xlsx, where the other field ([Account Owner]) comes from another file, which was mentioned in an earlier load statement.

where/How can I put this IF statement in the script ? 

Load Terrcode,
// Country,
// Sector,
// Industry
// Segment 
  [Calc. AO]

FROM
Territories.xlsx
(ooxml, embedded labels, table is Sheet1);

If ( [Calc. AO] <> [Account Owner] ,'Yes','No') as [AO change check]

[Anonymous]

Eric

Could you share the "earlier load statement" you mention ?

I suspect you'll need some kind of Join or Applymap, but it is hard to guess with the information given

Best Regards,     Bill

[Clever_Anjos]

Probably your tables share a key field.

As Bill Markham suggested you can join your tables, applymap a field or even use lookup function.

I´m using lookup here, assuming that both tables share Terrcode as a key

Table1:
Load
  mod(recno(),5) as Terrcode,
  rand() as [Calc. AO]
AutoGenerate 100;

Table2:
Load
  if( [Account Owner] <> Lookup('[Calc. AO]','Terrcode',Terrcode,'Table1'),
  'Yes','No') as [AO change check]

[Anonymous]

Bill,

Leaving out all the other fields I am loading from this file, the earlier statement is:

Opportunities:

Load

     Country,

     [Account Segment],

     Country&[Account Industry from SIC Code Lookup]&[Account Segment] as Terrcode

From

[SFDC opportunity export.xls]

biff, embedded labels);

As you can see, Terrcode is indeed the match key

[jagan]

Hi,

Try like this

OpportunitiesTemp:

Load

     Country,

     [Account Segment],

     [Account Owner],

     Country&[Account Industry from SIC Code Lookup]&[Account Segment] as Terrcode

From

[SFDC opportunity export.xls]

biff, embedded labels);

Left Join

Load Terrcode,
// Country,
// Sector,
// Industry
// Segment 
  [Calc. AO]
FROM
Territories.xlsx
(ooxml, embedded labels, table is Sheet1);

Opportunities:

LOAD

*,

If ( [Calc. AO] <> [Account Owner] ,'Yes','No') as [AO change check]

FROM OpportunitiesTemp;

DROP TABLE OpportunitiesTemp;

Regards,

Jagan.

[Anonymous]

Hi Jagan,

With your help and a colleague I now have the following working:

left join (Opportunities)
Load Terrcode,
// Country,
// Sector,
// Industry,
// Segment,  
  [Calc. AO]

FROM
Territories.xlsx
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Load

*,

If ( [Calc. AO] <> [Account Owner] ,'Yes','No') as [AO change check],
Resident Opportunities;

drop Table Opportunities;

rename Table Opportunities2 to Opportunities;

[jagan]

Hi,

Please close this discussion by giving Correct and Helpful answers to the post which helps in finding answers to you.  It helps others to find answers for similar type of scenarios.

Regards,

Jagan.