Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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