topic Re: looping and where condition help? in QlikView

looping and where condition help?

ganeshreddy — Wed, 28 Oct 2015 09:50:48 GMT

Hi All,

Can anyone explain , how can we add where condition for Wo_completionDate between the beginning of prior month and the end of current month(two months data like this month and previous month) and i need to put each KPI in a for loop to calculate PKI for each month.

Please check commented lines in code i.e where we need to implement the code .Please find the attached documents.

Code:

Summary:

LOAD wo_number,

pri_code,

wc_code,

Proj_number,

Client_source,

wo_completionDate

FROM

(ooxml, embedded labels, table is Sheet1);

let vMinMonth = min(num(month(floor(wo_completionDate)),'00'));

let vMaxMonth = max(num(month(floor(wo_completionDate)),'00'));

let vMinYear=min(year(floor(wo_completionDate)));

let vMaxYear=max(year(floor(wo_completionDate)));

//loop should start for each month KPI1 calculation

left join(Summary)

LOAD

Client_source,

count(wo_number) as KPI1

resident Summary where pri_code= '1'

//where condition has to be here for Wo_completionDate between the beginning of prior month and the end of current month

group by Client_source;

//loop should start for each month KPI2 calculation

left join(Summary)

LOAD

Client_source,

count(wo_number) as KPI1

resident Summary where pri_code= '2'

//where condition has to be here for Wo_completionDate between the beginning of prior month and the end of current month

group by Client_source;

Regards,

Ganesh

Re: looping and where condition help?

jonathandienst — Wed, 28 Oct 2015 09:58:42 GMT

The condition would be

Where Wo_completionDate >= MonthStart(Today(), -1) And Wo_completionDate <= MonthEnd(Today())

Re: looping and where condition help?

jonathandienst — Wed, 28 Oct 2015 10:01:38 GMT

Your second question is about the code. You cannot do a join inside a loop. This is because a join adds fields to the table. After the first iteration of the loop, the fields have already been added, so they will be considered part of the join key, rather than fields to add.

The solution is to build each table inside the loop, and then join the tables after the loop completes.

Re: looping and where condition help?

marcus_malinow — Wed, 28 Oct 2015 10:05:52 GMT

Ganesh,

I'm not going to attempt to answer your question here as I've posted some suggestions on a very similar thread you started a couple of days ago, and I don't think you understood what I suggested that time.

I will however point out what I think might be an error in your code.

Your Summary table includes wo_number (work order number?). Surely this is transaction level data, and not the level at which you want to summarise? Especially as your KPI 1 calculation is 'count(wo_number)'.

Marcus

Re: looping and where condition help?

ganeshreddy — Wed, 28 Oct 2015 10:39:48 GMT

Thanks for your suggestion jonathan, now i understood how to add looped data to summary table here. But in my first question about where clause is to get data for every month in data, not only for this month and previous month data. Your suggestion for where clause will only give september 2015 and october 2015 data. Actually this is where i got stuck in my previous thread as well.

Re: looping and where condition help?

ganeshreddy — Wed, 28 Oct 2015 10:45:07 GMT

Thank for you suggestion Marcus, I took your suggestions in my implementation, but i asked one more query on that thread which is similar to this thread, it may be a overlook. If i bothered you with my actions, sorry for that.

Best Regards,

Ganesh