topic Re: IF Statement in For Loop - Possible? in QlikView

IF Statement in For Loop - Possible?

Mon, 23 Jul 2012 14:22:34 GMT

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.......

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

Re: IF Statement in For Loop - Possible?

brenner_martina — Mon, 23 Jul 2012 14:41:15 GMT

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.......

Greetings from Munich

Martina

Re: IF Statement in For Loop - Possible?

Mon, 23 Jul 2012 14:42:58 GMT

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

Re: IF Statement in For Loop - Possible?

brenner_martina — Mon, 23 Jul 2012 14:45:04 GMT

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

Re: IF Statement in For Loop - Possible?

Mon, 23 Jul 2012 15:15:49 GMT

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