4 Replies Latest reply: Jun 18, 2012 11:19 AM by M Paeper RSS

    Coverting Aggr & set analysis expression for Load script?

    M Paeper

      I have created the following Pivot table expression which finally gives me the correct answer. However, it would be useful for me to have the result of this expression  loaded via script instead.

       

      How can I rewrite the expression below but as a LOAD script?

       

      Sum(Aggr((totalTime+overTime-absentTime),Operator)) - Sum({$<lostTimeId={AB2,AT,CR,ME,NS,NT,NW,RE,TR}>}duration)

       

      The SUM part I think I can do, the multiple IFs to replace the set analysis as well, buit how do I do the AGGR part?

       

      I can load data from a SQL db or QVD. Using QV 11SR1

       

      Thanks

       

      Cheers

        • Re: Coverting Aggr & set analysis expression for Load script?
          Christophe Brault

          Hi,

           

          If all your fields are in only one table, you could achieve that with a group by clause.

          ((totalTime+overTime-absentTime),Operator)) - Sum({$<lostTimeId={AB2,AT,CR,ME,NS,NT,NW,RE,TR}>}duration)

           

          TABLE_TEMP:

          LOAD totalTime,overTime,absentTime,Operator,lostTimeId,duration

          FROM ...

           

          TABLE:

          LOAD Operator,

          sum(totalTime)+sum(overTime)-sum(absentTime)-sum(if(lostTimeId=AB2 or lostTimeId=ATor lostTimeId=ME,duration,0)) as Time

          resident TABLE_TEMP group by Operator;

           

          I didn't have tested this script, but i think it have a chance.

            • Re: Coverting Aggr & set analysis expression for Load script?
              M Paeper

              Hi Christophe,

               

              Awesome, thank you. Your script idea should certainly get me on the right track I think. I hadnt used the Group by clause before. I managed to get a result with a few minor syntax changes. I don't get the same answer, as I get from my expression clause but I suspect thats possibly because the my underlying data groups might be a bit trickier than my post reveals since there are date fields etc as well. The value I'm guetting at present from the below code seems like its adding up all 5M records for that Operator, wheras I want it per production Date and if I add GROUP BY Operator, Date; the values get much smaller but still incorrect.

               

              I'll work on it a bit more and post if I find a resolution.

               

              [Temptable]:

              LOAD

              Operator,

              sum(totalTime)+sum(overTime)-sum(absentTime) - sum(if(lostTimeId='AB2' or lostTimeId='AT' or lostTimeId='CR' or lostTimeId='ME' or lostTimeId='NS' or lostTimeId='NT' or lostTimeId='NW' or lostTimeId='RE' or lostTimeId='TR', duration,0)) as OnStdTime

              RESIDENT [REOperationDayHistory] GROUP BY Operator;

            • Re: Coverting Aggr & set analysis expression for Load script?
              Hampus von Post

              Hi,

               

              You'd want to do this in a two-step rocket:

               

              What you want:

              ((totalTime+overTime-absentTime),Operator)) - Sum({$<lostTimeId={AB2,AT,CR,ME,NS,NT,NW,RE,TR}>}duration)

               

              TABLE_TEMP:

              LOAD totalTime,overTime,absentTime,Operator,lostTimeId,duration

              FROM ...

               

              TABLE:

              LOAD Operator,

              sum(totalTime)+sum(overTime)-sum(absentTime) as tempTotal

              resident TABLE_TEMP group by Operator;

               

              TABLE2:

              LEFT JOIN

              LOAD Operator,

              sum(duration) as tempDuration

              resident TABLE_TEMP where lostTimeId='AB2' or lostTimeId='AT' or ..... group by Operator;

               

              Results:

              LOAD Operator,

                   (tempTotal-tempDuration) as Time  //Your actual result

              resident TABLE;

              • Re: Coverting Aggr & set analysis expression for Load script?
                M Paeper

                Hi,

                 

                I'm not coming right with this, even with the suggestions received. Please can someone help me resolve.

                 

                Attached is a QVD of part of my data and the QVW (simplified to just include this issue and I've simplified it further to just the ClockTime calculation)

                 

                My problem is that while the set expression calculation works for individual dates on the Pivot table, when multiple dates are selected it shows a blank.

                 

                I would like the ClockTime shown for each day in the selection, so if I choose dates of 01 June - 05 June then I want to see the ClockTimes for those days displayed.

                 

                To explain the QVD data loaded, I have daily (CalendarDate) production activities (operationId) done by Operators (Operator) and those operators are grouped into a production line (workCenter).

                 

                Not shown in the data but it is in there somewhere is that operationIds aggregate the next lower level of detail called barcode - which are individual scans of parcels of work done. In the pivot I am skipping over this level of detail.

                 

                Ideally I'd like a drill down ability from CalendarDate -> workCenter -> Operator -> operationId with the data aggregated at each level.

                 

                However, aggregation at the CalendarDate -> workCenter level is the minimum requirement.

                 

                If one selects multiple dates then I want to see the total for the selection.

                 

                Examples below.

                 

                Drilldown works for a single date selection. e.g. 4 June. Everything seems correct except for the ScriptClockTime calculation. i.e. it should also show 660 in most cases in this example data.

                QlikView x64 - [X40 MODELSTestFR.png

                If I close the Pivot levels the total for AggrClockTime is correct but the ScriptClockTime calc is wrong.

                QlikView x64 - [X40 MODELSTestFR1.png

                If I now select 2 consecutive dates instead of seeing data for each date, only the first date shows anything but if I chose either 04/06/2012 or 05/06/2012 separately I would get the correct answer for AggrClockTime.

                QlikView x64 - [X40 MODELSTestFR2.png

                If I choose 3 consecutive dates everything shows me 0.

                QlikView x64 - [X40 MODELSTestFR3.png

                 

                Clearly an issue with how I'm aggregating. Ideally I'd like to do this aggregation (or at least most of it) in the LOAD script, but if I can't then how can I fix my Aggr formula in the pivot table.

                 

                Code is attached, but effectively I have this expression for AggrClockTime in the pivot table:

                 

                Sum(Aggr((totalTime+overTime-absentTime),Operator)) 
                

                 

                I have attempted to reproduce this logic as a LOAD by doing this.

                [REOperationDayHistory]:
                LOAD 
                    CalendarDate,
                    workCenter,
                    Operator,
                    IF(IsNull(operationId),lostTimeId & '-LostTime',operationId) as operationId, //replace Null values in operationId field
                    lostTimeId,
                    absentTime,
                    overTime,
                    totalTime
                FROM [\\QLIKVIEWSERVER\Qlikview\30 QVD SOURCE\REODHJune2012.qvd] (qvd);
                
                [Temptable]: // to try do Sum(Aggr((totalTime+overTime-absentTime),Operator)) as a LOAD script
                LEFT JOIN
                LOAD 
                Operator,
                sum(totalTime)+sum(overTime)-sum(absentTime) as ClockTime
                RESIDENT [REOperationDayHistory] GROUP BY Operator; 
                

                 

                I have tried various GROUP BY alternatives,

                 

                e.g. GROUP BY workCenter;

                or GROUP BY Operator, workCenter, CalendarDate; etc and nothing works.

                 

                It seems I can add additional levels to the GROUP BY separated by commas as in the previous example, but this doesnt seem to work any better and the QV help doesnt explain how I could use this.

                 

                Thanks,