5 Replies Latest reply: Oct 28, 2015 6:45 AM by Ganesh Reddy RSS

    looping and where condition help?

    Ganesh Reddy

      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

      [C:\Users\janaki\Desktop\TEST.xlsx]

      (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?
          Jonathan Dienst

          The condition would be

           

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

          • Re: looping and where condition help?
            Jonathan Dienst

            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?
                Ganesh Reddy

                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?
                Marcus Malinow

                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