Help with some SUMIF-type work

[twoplustwo]

Hi guys,

Hope you're all well.

I have a set of data that has 48 periods per record with +ive and -ive values.

I'd like to sum per settlement date across the range where each value is evaluated and summed if it is >0 in one column and <0 in t'other.

The processed set of data would look something like:

Date BuyVol SellVol

From a set of data like:

Date P1 P2 P3 P4 -> P48

Any suggestions appreciated.

I'm assuming I open the table in a recordset and evaluate each data point in a given record then, using an IF statement add it to either of the running totals per day but I'm not too sure where to start

 

[DCrake]

Simple Software Solutions

From what I can gleen from your post you have one record for each date with an additonal 48 fields and you want to perform a calculation one the 48 values.

You table structure is not normalised. You should have rows instead of columns.

Such as
PK :1
Date: 01.01.08
Ptype: A
Pvalue: 1
PK :1
Date: 01.01.08
Ptype:B
Pvalue: 2

etc

Then in your queries you can sum vertically instead of horizontally, making the formulas much easier.

CodeMaster:

 

[twoplustwo]

Hi DC,

I thought that was coming.

I agree that for most industries this is not in fact normalised data.

However, this is standard for the electricity industry in the UK - each period represents a half-hourly value. 3 of the major companies I've worked for operate this way, and National Grid do too :/

I mean - it's a straight recordset from our Oracle back-end, and it's what I have to work with

 

[DCrake]

Simple Software Solutions

Well if that is the case you can go down the Union route.

Union all the fields in the table into one field and use that for calculations

Eg:

Select Pk , FldDate, 1 As FldNo, P1 as PValue From Table
Union Select Pk, FldDate, 2 As FldNo, P2 As PValue From Table
Union Select Pk, FldDate, 3 As FldNo, P3 As PValue From Table
etc
Union Select Pk, FldDate, 48 As FldNo, P48 As PValue From Table

This way you are converting the flat file into a more manageable set of data.

Remember Rows are Free columns are expensive.

David

 

[twoplustwo]

Hi DC,

Just got the query working. Going to write the SUMIF part now. Thanks very much

Much appreciated!

I will def use this a lot in the future as transposing data is a very v ery common problem industry-wide.

 

[DCrake]

Simple Software Solutions

If you have picked up one thing via this thread then it has not been in vain.

David

 

[twoplustwo]

Hi DC,

I've hit an error with the sorting:

This data set:

[FONT=Arial][COLOR=#000000][B]tblDeltas[/B][/COLOR][/FONT][FONT=Arial][COLOR=#000000]Ruid[/COLOR][/FONT][FONT=Arial][COLOR=#000000]SettDate[/COLOR][/FONT][FONT=Arial][COLOR=#000000]ForDate[/COLOR][/FONT][FONT=Arial][COLOR=#000000]SumOfP1[/COLOR][/FONT][FONT=Arial][COLOR=#000000]SumOfP2[/COLOR][/FONT][FONT=Arial][COLOR=#000000]SumOfP3[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]0[/COLOR][/FONT][FONT=Arial][COLOR=#000000]0[/COLOR][/FONT][FONT=Arial][COLOR=#000000]0[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]15/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-277[/COLOR][/FONT][FONT=Arial][COLOR=#000000]1388[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-1056[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]16/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-4448[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-2319[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-3016[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]17/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]2746[/COLOR][/FONT][FONT=Arial][COLOR=#000000]1938[/COLOR][/FONT][FONT=Arial][COLOR=#000000]2054[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]18/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]1178[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-361[/COLOR][/FONT][FONT=Arial][COLOR=#000000]1388[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]19/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]235[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-1941[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-2749[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]20/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]25[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-2690[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-2576[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]21/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-949[/COLOR][/FONT][FONT=Arial][COLOR=#000000]862[/COLOR][/FONT][FONT=Arial][COLOR=#000000]494[/COLOR][/FONT][FONT=Arial][COLOR=#000000]~00007869[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]22/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]7713[/COLOR][/FONT][FONT=Arial][COLOR=#000000]6161[/COLOR][/FONT][FONT=Arial][COLOR=#000000]6744[/COLOR][/FONT]

And this SQL:

Select tblDeltas.SettDate, tblDeltas.ForDate, tblDeltas.SumOfP1  as P_Value from tblDeltas
Union Select tblDeltas.SettDate, tblDeltas.ForDate, tblDeltas.SumOfP2  as P_Value from tblDeltas

Gives these results, which don't look sorted by SettDate then by the ForDate then by Period (which we won't see).

[FONT=Arial][COLOR=#000000][B]qryTransposeDelta[/B][/COLOR][/FONT][FONT=Arial][COLOR=#000000]SettDate[/COLOR][/FONT][FONT=Arial][COLOR=#000000]ForDate[/COLOR][/FONT][FONT=Arial][COLOR=#000000]P_Value[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]0[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]15/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-1056[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]15/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-277[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]15/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]1388[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]16/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-4448[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]16/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-3016[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]16/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-2319[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]17/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]1938[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]17/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]2054[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]17/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]2746[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]18/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-361[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]18/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]1178[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]18/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]1388[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]19/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-2749[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]19/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-1941[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]19/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]235[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]20/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-2690[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]20/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-2576[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]20/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]25[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]21/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]-949[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]21/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]494[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]21/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]862[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]22/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]6161[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]22/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]6744[/COLOR][/FONT][FONT=Arial][COLOR=#000000]14/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]22/04/2008[/COLOR][/FONT][FONT=Arial][COLOR=#000000]7713[/COLOR][/FONT]

I tried the ORDER BY clause with two rows and had some joy but then added the rest of the SQL and it broke

 

[twoplustwo]

wtf please see attached

 

[twoplustwo]

Opps missed FldNumber off! Working now

Sorry to bother you David.