topic Re: where to put an if statement which includes 2 fields, loaded from different tables ? in QlikView

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

Anonymous — Sun, 24 Nov 2013 13:02:56 GMT

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]

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

Anonymous — Sun, 24 Nov 2013 13:27:57 GMT

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

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

Clever_Anjos — Sun, 24 Nov 2013 20:23:16 GMT

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]

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

Anonymous — Mon, 25 Nov 2013 06:39:48 GMT

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

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

jagan — Mon, 25 Nov 2013 07:21:41 GMT

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.

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

Anonymous — Mon, 25 Nov 2013 11:50:23 GMT

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;

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

jagan — Tue, 26 Nov 2013 02:44:52 GMT

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.