IF Statement in For Loop - Possible?

[#1]

Ive got the below loop in my script which works great

for a = 0 to NoOfRows('tmp_Country_TBL') - 1

     let vBU = peek('vBUEntityCode',$(a),'tmp_Country_TBL');

          [Periods - Dates]:

          LOAD

               '$(vBU)' & '-' & date(MAX([$PERIOD_IMS_tmp])) as [$SETID_PERIOD],

               'Latest Mth (CY)' as [Date-Period]

          Resident [Sales Data]

          Where [BU-Entity ID] = '$(vBU)';

     //LOTS MORE CODE.......

Next

but need to expand the WHERE clause when the vBU variable is something specific....i.e the complete clause will be:

          Where [BU-Entity ID] = '$(vBU)' and [BU-Region] = 'Northen Ireland'

How can I integrate this in the loop?

Thanks, Jay

[brenner_martina]

Hi, Jay,

I think you have to integrate your statement like this:

for a = 0 to NoOfRows('tmp_Country_TBL') - 1

     let vBU = peek('vBUEntityCode',$(a),'tmp_Country_TBL');

      If '$(vBU)' = 'XYZ' Then

          [Periods - Dates]:

          LOAD

               '$(vBU)' & '-' & date(MAX([$PERIOD_IMS_tmp])) as [$SETID_PERIOD],

               'Latest Mth (CY)' as [Date-Period]

          Resident [Sales Data]

          Where [BU-Entity ID] = '$(vBU)' And [BU-Region] = 'Northern Ireland';

     Else

          [Periods - Dates]:

          LOAD

               '$(vBU)' & '-' & date(MAX([$PERIOD_IMS_tmp])) as [$SETID_PERIOD],

               'Latest Mth (CY)' as [Date-Period]

          Resident [Sales Data]

          Where [BU-Entity ID] = '$(vBU)';

     EndIf

     //LOTS MORE CODE.......

Next

Greetings from Munich

Martina

[#3]

Add a condition in the script.

for a = 0 to NoOfRows('tmp_Country_TBL') - 1
     let vBU = peek('vBUEntityCode',$(a),'tmp_Country_TBL');
     if $(vBU) = 'ABC' 
          [Periods - Dates]:
          LOAD
               '$(vBU)' & '-' & date(MAX([$PERIOD_IMS_tmp])) as [$SETID_PERIOD],
               'Latest Mth (CY)' as [Date-Period]
          Resident [Sales Data]
          Where [BU-Entity ID] = '$(vBU)' and [BU-Region] = 'Northen Ireland'; 
     ELSE
                 [Periods - Dates]:
          LOAD
               '$(vBU)' & '-' & date(MAX([$PERIOD_IMS_tmp])) as [$SETID_PERIOD],
               'Latest Mth (CY)' as [Date-Period]
          Resident [Sales Data]
          Where [BU-Entity ID] = '$(vBU)';
     ENDIF
Next

Regards,

Kiran

[brenner_martina]

Hi, Kiran,

but pardon, in your statement I miss "Then" behind If '$(vBU)' = 'ABC'!!! And the Quotes for $(vBU)!!!

...

If '$(vBU)' = 'ABC' Then

...

Greetings from Munich

Martina

[#5 · Accepted solution]

I added an if statement checking the value of vBU and based on the resut set a new variabale which I added to the WEHRE clause.

This prevents duplicating code in the ELSE part of the statement.

Thanks, Jay