topic Re: Aggregated Positions in QlikView

Aggregated Positions

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregated Positions

Anil_Babu_Samineni — Thu, 13 Oct 2016 10:55:12 GMT

Try Like This

sum(TOTAL udf_code)

Sum(Total [Leg 1])

Sum(Total [Leg 2])

Re: Aggregated Positions

danielnevitt — Thu, 13 Oct 2016 10:57:39 GMT

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

Re: Aggregated Positions

Anil_Babu_Samineni — Thu, 13 Oct 2016 11:00:16 GMT

NP, Qlikview can take where field has same Values

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

Re: Aggregated Positions

danielnevitt — Thu, 13 Oct 2016 11:01:43 GMT

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

Appreciate the help.

Regards,

Daniel

Re: Aggregated Positions

sunny_talwar — Thu, 13 Oct 2016 11:03:48 GMT

I think you need to use a Mapping Load here

Re: Aggregated Positions

danielnevitt — Thu, 13 Oct 2016 11:06:53 GMT

Hi Sunny

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

Regards,

Daniel

Re: Aggregated Positions

sunny_talwar — Thu, 13 Oct 2016 11:09:15 GMT

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

];

Re: Aggregated Positions

danielnevitt — Thu, 13 Oct 2016 11:21:27 GMT

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

Re: Aggregated Positions

sunny_talwar — Thu, 13 Oct 2016 11:30:58 GMT

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

Re: Aggregated Positions

danielnevitt — Thu, 13 Oct 2016 14:55:44 GMT

Hi,

Is there a way of taking the data from the first tab (on the attached spreadsheet) and creating the view of the data as per the second tab?

I'm not sure if it's possible to use the data from one table to create another?

If not, how can I create a table that list all of the codes from 'udf_code', 'Spot-Month Aggregate Into Futures Equivalent Leg (1)' and 'Spot-Month Aggregate Into Futures Equivalent Leg (2)'?

If this can be shown using a .qvw example that would be great?

Regards,

Daniel

Re: Aggregated Positions

sunny_talwar — Thu, 13 Oct 2016 19:03:44 GMT

May be you want this?

Table:

LOAD udf_code,

[sum(unpriced_lots)],

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

[Total(Leg 1)],

[Spot-Month Aggregate Into Futures Equivalent Leg (2)],

[Total (Leg 2)]

FROM

[Position test data.xlsx]

(ooxml, embedded labels, table is [Original Table]);

FinalTable:

LOAD udf_code as code,

[sum(unpriced_lots)] as lots

Resident Table;

Concatenate(FinalTable)

LOAD [Spot-Month Aggregate Into Futures Equivalent Leg (1)] as code,

[Total(Leg 1)] as lots

Resident Table;

Concatenate(FinalTable)

LOAD [Spot-Month Aggregate Into Futures Equivalent Leg (2)] as code,

[Total (Leg 2)] as lots

Resident Table;

DROP Table Table;

Re: Aggregated Positions

danielnevitt — Fri, 14 Oct 2016 09:46:26 GMT

Thanks Sunny, I wrote a very similar solution.

I had an idea to create the table in one .qvw and export to a .qvd file on a daily basis. Is it possible to export a specific table as part of the script or automated process?

I will then re-load the data from the .qvd file in a new .qvw file.

Regards,

Daniel

Re: Aggregated Positions

sunny_talwar — Fri, 14 Oct 2016 12:39:34 GMT

You can store the data using a STORE statement

Table:

LOAD udf_code,

[sum(unpriced_lots)],

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

[Total(Leg 1)],

[Spot-Month Aggregate Into Futures Equivalent Leg (2)],

[Total (Leg 2)]

FROM

[Position test data.xlsx]

(ooxml, embedded labels, table is [Original Table]);

FinalTable:

LOAD udf_code as code,

[sum(unpriced_lots)] as lots

Resident Table;

Concatenate(FinalTable)

LOAD [Spot-Month Aggregate Into Futures Equivalent Leg (1)] as code,

[Total(Leg 1)] as lots

Resident Table;

Concatenate(FinalTable)

LOAD [Spot-Month Aggregate Into Futures Equivalent Leg (2)] as code,

[Total (Leg 2)] as lots

Resident Table;

STORE FinalTable into Path/QVDName.qvd (qvd);

DROP Table Table, FinalTable;

Re: Aggregated Positions

danielnevitt — Fri, 14 Oct 2016 13:51:48 GMT

Hi Sunny,

Sorry I am really struggling with this one.

I have the below code which is join to an ODBC sql (large code) on udf_code. I have a field in the sql named unpriced_lots that is used in the new table.

Table:

left join LOAD [Contract Name],
[Rule Chapter],
[Commodity Code] as udf_code,
[Contract Size],
[Contract Units],
Type,
Settlement,
Group,
[Diminishing Balance Contract],
[Reporting Level],
[Spot-Month position comprised of futures and deliveries],
[Spot-Month Aggregate Into Futures Equivalent Leg (1)],
[Spot-Month Aggregate Into Futures Equivalent Leg (2)],
[Spot-Month Aggregate Into Ratio Leg (1)],
[Ratio Leg (1)],
[Spot-Month Aggregate Into Ratio Leg (2)],
[Ratio Leg (2)],
[Spot-Month Accountability Level],
[Daily Accountability Level (For Daily Contract)],
[Initial Spot-Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)],
[Initial Spot-Month Limit Effective Date],
[Spot-Month Limit (In Contract Units) Leg (1) / Leg (2)],
[Second Spot-Month Limit (In Net Futures Equivalents)],
[Second Spot-Month Limit Effective Date],
[Single Month Aggregate Into Futures Equivalent Leg (1)],
[Single Month Aggregate Into Futures Equivalent Leg (2)],
[Single Month Aggregate Into Ratio Leg (1)],
[Single Month Aggregate Into Ratio Leg (2)],
[Single Month Accountability Level Leg (1) / Leg (2)],
[Single Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)],
[All Month Aggregate Into Futures Equivalent Leg (1)],
[All Month Aggregate Into Futures Equivalent Leg (2)],
[All Month Aggregate Into Ratio Leg (1)],
[All Month Aggregate Into Ratio Leg (2)],
[All Month Accountability Level Leg (1) / Leg (2)],
[All Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)]

FROM

(ooxml, embedded labels, header is 1 lines, table is [Ch 5 Table]);

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 TotalLeg1

Resident Table;

DROP Table Table, FinalTable;

However I get the following error message. Do you have any idea what I am doing wrong?

Regards,

Daniel

Re: Aggregated Positions

sunny_talwar — Fri, 14 Oct 2016 13:54:34 GMT

Why do you have a left join here?

Is that needed for something unrelated?

Re: Aggregated Positions

danielnevitt — Fri, 14 Oct 2016 13:59:46 GMT

It might not be needed. I have taken it out and now just have LOAD, however I get the below error message:

unpriced_lots is a field calculated in the sql (see below):

convert(numeric(23,8),sum(_UDF_SPEC_v.udf_pos_lots * (case when _exposure.priced_eod_ind='N' then _exposure.pricing_pct else 0 end))) as unpriced_lots

Regards,

Daniel

Re: Aggregated Positions

sunny_talwar — Fri, 14 Oct 2016 14:03:44 GMT

But it is not available in your Table right from which you are doing a resident load?

Re: Aggregated Positions

danielnevitt — Fri, 14 Oct 2016 14:06:18 GMT

It's in the SQL that I join to the first table.

I was trying to then create a table from this joined data. Is that possible?

Regards,

Daniel

Re: Aggregated Positions

sunny_talwar — Fri, 14 Oct 2016 14:22:29 GMT

It is... try this...

SQLTable

SQL Select ...;

Left Join (SQLTable)

LOAD [Contract Name],
[Rule Chapter],
[Commodity Code] as udf_code,
[Contract Size],
[Contract Units],
Type,
Settlement,
Group,
[Diminishing Balance Contract],
[Reporting Level],
[Spot-Month position comprised of futures and deliveries],
[Spot-Month Aggregate Into Futures Equivalent Leg (1)],
[Spot-Month Aggregate Into Futures Equivalent Leg (2)],
[Spot-Month Aggregate Into Ratio Leg (1)],
[Ratio Leg (1)],
[Spot-Month Aggregate Into Ratio Leg (2)],
[Ratio Leg (2)],
[Spot-Month Accountability Level],
[Daily Accountability Level (For Daily Contract)],
[Initial Spot-Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)],
[Initial Spot-Month Limit Effective Date],
[Spot-Month Limit (In Contract Units) Leg (1) / Leg (2)],
[Second Spot-Month Limit (In Net Futures Equivalents)],
[Second Spot-Month Limit Effective Date],
[Single Month Aggregate Into Futures Equivalent Leg (1)],
[Single Month Aggregate Into Futures Equivalent Leg (2)],
[Single Month Aggregate Into Ratio Leg (1)],
[Single Month Aggregate Into Ratio Leg (2)],
[Single Month Accountability Level Leg (1) / Leg (2)],
[Single Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)],
[All Month Aggregate Into Futures Equivalent Leg (1)],
[All Month Aggregate Into Futures Equivalent Leg (2)],
[All Month Aggregate Into Ratio Leg (1)],
[All Month Aggregate Into Ratio Leg (2)],
[All Month Accountability Level Leg (1) / Leg (2)],
[All Month Limit (In Net Futures Equivalents) Leg (1) / Leg (2)]

FROM

(ooxml, embedded labels, header is 1 lines, table is [Ch 5 Table]);

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 TotalLeg1

Resident SQLTable;

Are you now going to store this new table in qvd or not? If you do want to save it, add a store statement before you drop FinalTable