Aggregated Positions

[danielnevitt]

Hi,

I have the attached data and I am struggling to come up with a solution as to how to aggregate the positions.

There are three totals columns:

sum(udf_code)

Total (Leg 1)

Total (Leg 2)

Each of these totals has an applicable code (columns A, D and F)

I would like to return the results as listed on the Total tab.

Any help will be much appreciated.

Regards,

Daniel

[Anil_Babu_Samineni]

Try Like This

sum(TOTAL udf_code)

Sum(Total [Leg 1])

Sum(Total [Leg 2])

[danielnevitt]

Some of the codes appear in different columns.  For example '23' appears in column D and F.

Therefore is there a clever way to calculate the totals for this.

Thanks,

Daniel

[Anil_Babu_Samineni]

NP, Qlikview can take where field has same Values

Give Alias Name to change the NAmes and use Qualify and UnQualify to achieve this

[danielnevitt]

Thanks Anil.  Are you able to use my data as an example?

Appreciate the help.

Regards,

Daniel

[sunny_talwar]

I think you need to use a Mapping Load here

[danielnevitt]

Hi Sunny

Are you able to provide a bit more information.  I am unsure how to do this.

Regards,

Daniel

[sunny_talwar]

Try this:

MappingTable:

Mapping

LOAD * INLINE [

    udf_code, Total

    23, 225

    26, -30

    5Y, -15

    7Y, -215

    GY, 20

    HK, 10

    LT, -200

    LY, 20

];

FactTable:

LOAD *,

  ApplyMap('MappingTable', udf_code) as [sum (udf_code)],

  ApplyMap('MappingTable', [Spot-Month Aggregate Into Futures Equivalent Leg (1)]) as [Total (Leg 1)],

  ApplyMap('MappingTable', [Spot-Month Aggregate Into Futures Equivalent Leg  (2)]) as [Total (Leg 2)];

LOAD * INLINE [

    udf_code, formatdate, Spot-Month Aggregate Into Futures Equivalent Leg (1), Spot-Month Aggregate Into Futures Equivalent Leg  (2)

    7Y, OCT-16, 5Y, 23

    GY, OCT-16, LY, 26

    HK, OCT-16, 23, 26

    LT, OCT-16, LY, 23

];

[danielnevitt]

Thanks Sunny, much appreciated.

In excel I would use the following code:

=SUMIF(Data!A:A,Total!$A2,Data!B:B)+SUMIF(Data!D:D,Total!$A2,Data!$E:$E)+SUMIF(Data!F:F,Total!$A2,Data!G:G)

Is it possible to use a similar code in Qlikview?

Thanks,

Daniel

[sunny_talwar]

You can do this, I guess:

RangeSum(

ApplyMap('MappingTable', udf_code) as [sum (udf_code)],

ApplyMap('MappingTable', [Spot-Month Aggregate Into Futures Equivalent Leg (1)]) as [Total (Leg 1)],

ApplyMap('MappingTable', [Spot-Month Aggregate Into Futures Equivalent Leg  (2)]) as [Total (Leg 2)]

) as TOTAL