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: 
Not applicable

Coverting Aggr & set analysis expression for Load script?

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

4 Replies
christophebrault
Specialist
Specialist

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.

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Not applicable
Author

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;

hps
Employee
Employee

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;

Not applicable
Author

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,