Solved: Re: Aggregated Positions - Qlik Community

danielnevitt · ‎2016-10-13

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

sunny_talwar · ‎2016-10-14

FinalTable:

LOAD exchange_cd,
    [Commodity Code] as udf_code,
    formatdate,
    Sum(unpriced_lots) as SumUnpricedLots,
    [Spot-Month Aggregate Into Ratio Leg (1)],
    Sum(unpriced_lots*[Ratio Leg (1)]) as TotalLeg1,
    [Spot-Month Aggregate Into Ratio Leg (2)],
    Sum(unpriced_lots*[Ratio Leg (2)]) as TotalLeg2

Resident SQLTable

Group By

exchange_cd,

[Commodity Code],

formatdate,

[Spot-Month Aggregate Into Ratio Leg (1)],

[Spot-Month Aggregate Into Ratio Leg (2)];

View solution in original post

Anil_Babu_Samineni · ‎2016-10-13

Try Like This

sum(TOTAL udf_code)

Sum(Total [Leg 1])

Sum(Total [Leg 2])

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

danielnevitt · ‎2016-10-13

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 · ‎2016-10-13

NP, Qlikview can take where field has same Values

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

danielnevitt · ‎2016-10-13

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

Appreciate the help.

Regards,

Daniel

sunny_talwar · ‎2016-10-13

I think you need to use a Mapping Load here

danielnevitt · ‎2016-10-13

Hi Sunny

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

Regards,

Daniel

sunny_talwar · ‎2016-10-13

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 · ‎2016-10-13

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 · ‎2016-10-13

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