How to declare variables while using Group by

[madhubabum]

Hi Experts

Here i am using Group by with fd_Code only .

Temp_month:

load num(min(Month(wo_completionDate))) as minMonth,

     num(max(Month(wo_completionDate))) as maxMonth

from $(vQVDDataDir)\ARMK_Facilities_Optimization_WO.qvd (qvd) where year(floor(wo_completionDate))='2015' ;

let vMinMonth= peek('minMonth');

let vMaxMonth= peek('maxMonth');

let i=$(vMinMonth);

Vaiable_tab:

CrossTable(Variables, Value)

LOAD s.n,

     pri_code,

     wc_code,

     wo_Diff

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

for j=0 to NoOfRows('Vaiable_tab')-1

LET vName = Peek('Variables', j, 'Vaiable_tab');

LET $(vName) = Peek('Value', j, 'Vaiable_tab');

let vSN= peek('s.n', j, 'Vaiable_tab');

do while i<=$(vMaxMonth)

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     if(pri_code=1 and  wc_code = '01' and wo_Diff <='1' ,count(wo_number)) as [No Of Work Orders 1],

     if(pri_code=1 and  wc_code = '01'  ,count(wo_number)) as [No Of Work Orders 1.1],

     if(pri_code=2 and  wc_code = '01' and wo_Diff <=2,count(wo_number)) as [No Of Work Orders 2],

     if(pri_code=2 and  wc_code = '01' ,count(wo_number)) as [No Of Work Orders 2.1],

     if(pri_code='$(j)' and  wc_code = '$(j)' and wo_Diff <='$(j)',count(wo_number)) as [No Of Work Orders 3],

     if(pri_code='$(j)' and  wc_code = '$(j)' and wo_Diff <='$(j)',count(wo_number)) as [No Of Work Orders 3.1],

     if(pri_code='$(j)' and  wc_code = '$(j)' and wo_Diff <='$(j)',count(wo_number)) as [No Of Work Orders 4],

     if(pri_code='$(j)' and  wc_code = '$(j)' and wo_Diff <='$(j)',count(wo_number)) as [No Of Work Orders 4.1]

     from $(vQVDDataDir)\ARMK_Facilities_Optimization_WO.qvd (qvd)

     where num(month(floor(wo_completionDate))) >= '$(i)'-1 and num(month(floor(wo_completionDate))) <= '$(i)' and year(floor(wo_completionDate))='2015'  

     and isnull(proj_number) //and pri_code='$(j)' and  wc_code = '$(j)' and wo_Diff <='$(j)'

     group By

     fd_code ;

     Let i=i+1;

LOOP

next j

LET j = Null();

LET vName = Null();

Error :

How we can use Variables in If Statement ..?

Thanks

Madhu

[sasikanth · Accepted solution]

Try Exps like this

Count(if(pri_code=1 and  wc_code = '01' and wo_Diff <='1' , wo_number)) as [No Of Work Orders 1],