topic Re: Using aggregated fields for further processing in QlikView

Using aggregated fields for further processing

Wed, 22 Jan 2014 22:02:47 GMT

Hi,

I have a few aggregated fields in my load script and want to do some calculations on these fields:

.....

LOAD *,

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, 0,[MedianModTemp]) as [CommCorrectedModTemp],

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0, 0, [Energy Diff]) as [CommCorrectedEnergy],

If([Energy Diff] = 0 OR [MedianModTemp] = 0 OR [MedianIrr] = 0,0, [MedianIrr]) as [CommCorrectedIrr]

;

LOAD Project,

...

Energy,

Irr1,

Irr2,

ModTemp1,

ModTemp2,

if (IsNull(Energy),0, Energy) - (if (IsNull (Previous(Energy)),0,Previous(Energy))) as [Energy Diff],

(if (IsNull(ModTemp1),0,ModTemp1) + if(IsNull(ModTemp2),0,ModTemp2))/2 as [MedianModTemp],

(if (IsNull(Irr1),0,Irr1) + if(IsNull(Irr2),0,Irr2))/2 as [MedianIrr]

...

[SUMMATION]:

LOAD

Sum(CommCorrectedEnergy) as [Daily Energy],

Date

Resident MAIN

Group By Date

;

LOAD

Sum(CommCorrectedIrr) as [Daily IRR],

Date

Resident MAIN

Group By Date

;

LOAD

Sum(CommCorrectedModTemp) as [Daily ModTemp],

Date

Resident MAIN

Group By Date

;

I want to do the following

[GetIns]:

LOAD

[Daily modTemp]/[Daily IRR] as [ModTempIns]

;

I get an error when I do this. Where should I be placing this? If I place before [SUMMATION], I get an error, if I place in the end after [SUMMATION], I don't get an error, but it does not seem to be doing anything as I don't see the field [ModTempIns] created.

Thanks

Srikanth

Re: Using aggregated fields for further processing

Wed, 22 Jan 2014 22:05:49 GMT

Hey Srikanth,

You could be getting an error message as the fields used for Calculating [ModTempIns] are created after summation. So the place where you are doing this is correct.

Also make sure you are not dropping the table/field. Also please check the Table viewer and look for the field in that table.

Re: Using aggregated fields for further processing

Wed, 22 Jan 2014 22:37:30 GMT

Thank you, Ajay.

Adding it at the end of the script does not give an error, but the field is not visible/accessible either. It is not in Table viewer as well.

...

LOAD

Sum(CommCorrectedModTemp) as [Daily ModTemp],

Date

Resident MAIN

Group By Date

;

[GetIns]:

LOAD

[Daily ModTemp]/ [Daily IRR] as [ModTempIns]

;

Re: Using aggregated fields for further processing

Wed, 22 Jan 2014 22:41:53 GMT

Please try this as the summation table. Its a preload:

[SUMMATION]:

Load

[Daily modTemp]/[Daily IRR] as [ModTempIns] ;

LOAD

Sum(CommCorrectedEnergy) as [Daily Energy],

Sum(CommCorrectedIrr) as [Daily IRR],

Sum(CommCorrectedModTemp) as [Daily ModTemp],

Date

Resident MAIN

Group By Date ;

Re: Using aggregated fields for further processing

Wed, 22 Jan 2014 23:49:17 GMT

Perfect, Thanks