Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ganeshreddy
Creator III
Creator III

looping and where condition help?

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

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

The condition would be

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

ganeshreddy
Creator III
Creator III
Author

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.

ganeshreddy
Creator III
Creator III
Author

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