How to reduce this Code with a single loop

[madhubabum]

HI Experts

Here we are calculating the Metrics with loop

Note : But we want to calculate with single loop instead of multiple loops  (We need to reduce the loops)

For Example : I am calculating the Count of Workorders  for each Metric with individual loops

                     Here i want to calculate Count of WorkOrders  for each Metric in a single loop according to Business 

                     Requirement.

Note : Here Metrics are calculated with different Filters

Added the code bellow....

Summary:

Load

    fd_code as [Client source],

    num(month(floor(wo_completionDate))) as Month,

    count(wo_number) as [Count_of_records]

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

group by fd_code, num(month(floor(wo_completionDate)));

Temp_month:

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

     num(max(month(wo_completionDate))) as maxMonth

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

let vMinMonth= peek('minMonth');

let vMaxMonth= peek('maxMonth');

let i=$(vMinMonth);

do while i<=$(vMaxMonth)

                                           //////  Metrics Starts from Here //////

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     count(wo_number) as [No Of Work Orders 1]

     from $(vQVDDataDir)\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= 1 and  wc_code = '01' and wo_Diff <=1

     group By

     fd_code

     ;

     Let i=i+1;

LOOP

//drop table Temp_month;

let i=$(vMinMonth);

do while i<=$(vMaxMonth)

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     count(wo_number) as [No Of Work Orders 1.1]

     from $(vQVDDataDir)\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= 1 and  wc_code = '01'

     group By

     fd_code

     ;

     Let i=i+1;

LOOP

let i=$(vMinMonth);

do while i<=$(vMaxMonth)

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     count(wo_number) as [No Of Work Orders 2]

     from $(vQVDDataDir)\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= 2 and  wc_code = '01' and wo_Diff <=2

     group By

     fd_code

     ;

     Let i=i+1;

LOOP

let i=$(vMinMonth);

do while i<=$(vMaxMonth)

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     count(wo_number) as [No Of Work Orders 2.1]

     from $(vQVDDataDir)\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= 2 and  wc_code = '01'

     group By

     fd_code

     ;

     Let i=i+1;

LOOP

let i=$(vMinMonth);

do while i<=$(vMaxMonth)

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     count(wo_number) as [No Of Work Orders 3]

     from $(vQVDDataDir)\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= 3 and  wc_code = '01' and wo_Diff <=28

     group By

     fd_code

     ;

     Let i=i+1;

LOOP

let i=$(vMinMonth);

do while i<=$(vMaxMonth)

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     count(wo_number) as [No Of Work Orders 3.1]

     from $(vQVDDataDir)\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= 3 and  wc_code = '01'

     group By

     fd_code

     ;

     Let i=i+1;

LOOP

let i=$(vMinMonth);

do while i<=$(vMaxMonth)

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     count(wo_number) as [No Of Work Orders 4]

     from $(vQVDDataDir)\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= 4 and  wc_code = '01' and wo_Diff <=14

     group By

     fd_code

     ;

     Let i=i+1;

LOOP

let i=$(vMinMonth);

do while i<=$(vMaxMonth)

join(Summary)

load

     '$(i)' as Month,

     fd_code as [Client source],

     count(wo_number) as [No Of Work Orders 4.1]

     from $(vQVDDataDir)\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= 4 and  wc_code = '01'

     group By

     fd_code

     ;

     Let i=i+1;

LOOP

drop table Temp_month;

STORE Summary INTO [$(vQVDDataDir)\Summary.qvd] (qvd);

Thanks

Madhu

[marcus_sommer]

I think you could reduce the number of loop-parts if you put your do while loop within a for each loop and where the in-list contained your different filter criteria and fieldnames - if you loaded these filter criteria and fieldnames into a table and runs then loop through the field-values you could use a normal for loop as well.

But before I suggest you rethink your whole datamodel then you creates a crosstable which is a rather seldom really suitable. Quite often you could with a normal table-structure easily calculate your needs within the gui.

- Marcus

[madhubabum]

HI Marcus Sommer

I think your suggestion will work for this scenario, Can you explain me below point with pseudo code

'put your do while loop within a for each loop and where the in-list contained your different filter criteria and fieldnames - if you loaded these filter criteria and fieldnames into a table and runs then loop through the field-values you could use a normal for loop as well.'

Here our basic idea is to create a summary table which consists of client source, month, metric1, metric 2 ,.....and so on.

ex:

  

Thanks,

Best Regards,

Madhu.

[marcus_sommer]

Have a look here Re: load variable in qlikview on the answer from sunindia how a table of variables (in your case would it be the filter criteria and fieldnames) could be loaded and then read within a loop and in this loop you would set your do while loop.

But I think your screenshot from the table showed it quite clear that these table-structure isn't really optimal - the most are NULL's.

- Marcus