How to declare variables while using Group by

madhubabum — Mon, 02 Nov 2015 06:09:48 GMT

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

Re: How to declare variables while using Group by

sasikanth — Mon, 02 Nov 2015 06:43:58 GMT

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],

topic How to declare variables while using Group by in QlikView

How to declare variables while using Group by

Re: How to declare variables while using Group by