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 Sunny,

Thanks for all your help.

Unfortunately I am recieving the below error message:

Do you know how I can resolve this?

Regards,

Daniel

[sunny_talwar]

I think you called TotalLeg1 twice. Rename the other one to TotalLeg2?

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;

[danielnevitt]

Thanks Sunny.

It now says that it is an invalid expression:

Regards,

Daniel

[sunny_talwar · Accepted solution]

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)];

[danielnevitt]

Thanks again for the response.

Unfortunately I am still getting an invalid expression:

Regards,

Daniel

[sunny_talwar]

Do you have Sum here? or not

[danielnevitt]

Thank you Sunny, that now works perfectly.

Am I correct in my understanding that I would add the following to the script to create the .qvd file?

FinalTable:

LOAD exchange_cd,
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,
udf_code,
formatdate,
[Spot-Month Aggregate Into Ratio Leg (1)],
[Spot-Month Aggregate Into Ratio Leg (2)];

STORE FinalTable into \\rosie\compliance\QlikView.qvd (qvd);

Regards,

Daniel

[sunny_talwar]

Seems about right to me.

[danielnevitt]

Thank you so much for all your help.

Everything now works perfectly apart from the STORE to .qvd.  There is no error, however for some reason the file doesn't save into the location.

Regards,

Daniel

[sunny_talwar]

I see two black slashes at in your path

May be you need this:

STORE FinalTable into rosie\compliance\QlikView.qvd (qvd);