How to reduce this Code with a single loop - Qlik Community

madhubabum · ‎2015-11-01

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 · ‎2015-11-01

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 · ‎2015-11-01

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 · ‎2015-11-01

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