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

[danielnevitt]

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

[sunny_talwar]

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;

[danielnevitt]

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

[sunny_talwar]

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;

[danielnevitt]

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

[sunny_talwar]

Why do you have a left join here?

Is that needed for something unrelated?

[danielnevitt]

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

[sunny_talwar]

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

[danielnevitt]

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

[sunny_talwar]

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