8 Replies Latest reply on Oct 21, 2011 6:50 AM by OkcunSahin

# Trying to create table for filling the empty data and not modifying them. But the calculation...

### Title

Trying to create table for filling the empty data and not modifying them. But the calculation doesn't work.

### Post

I have a case;

I have customers which has a unique id. These customers have some different facilities on different locations and these locations have a unique location id. To produce an efficiency test We are getting 4 datas as (morning + afternoon + night) or 24 hour total and for 12 months.

To start a relationship with a new customer; I have to first evaluate the data to give an offer but data is missing. Some of them has 12 months of 24 hour total, some of them has missing (morning + afternoon + night) and some of them has only one month value of 24 hour. My problem is I have to evaluate it from over (morning + afternoon + night) and for over 12 months. For that I have created a validation list. Which has fields as June Morning, June Afternoon, June night; June 24 Hours. And created this formula but it is not working and I can not understand why.

Case( location::_month = 1 and location::_morning > 0 ; location::_morning ;

location::_month = 1 and location::_morning = "" ; location_24 * 0.2 ;

location::_month > 1 ; Average(location::_morning) ;

location::_month > 1 ; Average(location::_morning) = 0 ; Average(location_24) * 0.2 )

My problem it works when the month is 1 but when it comes to 2 or others it is not working.

• ###### 1. Re: Trying to create table for filling the empty data and not modifying them. But the calculation...

What is the relationship between Customer and Location?

I would guess it to be:

Customer::CustomerID = Location::CustomerID

Is this calculation defined in Customer?

If so and if I have correctly guessed the relationship, you have a one to many relationship.

Any reference to a Location record from a calculation field defined in customer will only reference the "first" location record with a matching customer ID value. Any additional records with this same ID will be invisible to this calculation.

To resolve this issue, I'd need to know a lot more about what you are attempting to do with this calculation. Off hand, it looks like something a summary report layout based on location may be able to do for you, but I'm making a lot of guesses when I suggest that.

• ###### 2. Re: Trying to create table for filling the empty data and not modifying them. But the calculation...

Well you are correct on your guest. Both of them are holding the same Customer ID but the thing that I want to get is; The customers are electricity consumers. Before I give an offer to them, I want to find out their cost by using some other factors as Electricity Market price.

As you may know electricity market prices differs by hour to hour and month to month. The agreement is 12 months so I have to evaluate it over 12 months without a gap on the hour. If the customer doesn't have required data as 12 months and (morning + afternoon + night) of past consumption, Then I have to create data with a special technic and give the offer.

Can it be solved?

• ###### 3. Re: Trying to create table for filling the empty data and not modifying them. But the calculation...

It can be done, but I don't have all the details I need to discribe the solution.

Are you trying to compute an average over 12 months of past consumption?

Are you logging each figure (morning, afternoon, night) in a separate record?

That would make for 12 x 3 = 36 records so you can check the number of records as a way to confirm you have all the data. That "Special technic" is something I know nothing about so I can't comment much on how to implement that here. Presumably, you could use this algorithm to generate the missing records, but that's just a guess at this point.

If you need an average of these 36 months, Average ( Location::Consumption ) would return the average. Sum returns a total and other aggregate functions exist.

Very similar approaches can also be set up that work with summary fields defined in the location table. A summary report using such fields and based on the location table, may in fact be an option here.

But I'd need to know more to be sure.

• ###### 4. Re: Trying to create table for filling the empty data and not modifying them. But the calculation...

Yes correct we are talking about 36 records which will be checked and validated in another table

Let me give you and example of the combinations that can come;

1. For July_morning; The field morning can be full, 24 hours can be full. Then it will put the value of July Morning in the new table field as July_morning
2. For July_morning; The field morning can be empty, 24 hours can be full. Then it will use the value of July 24 and calculate it with a value as 0,2 for example and put it in the new table field as July_morning
3. For july_morning; The field morning can be empty, 24 hours can be empty (So it means no record for that month for that location). Then it will take the average morning of other months
4. For July Morning; The field morning can be empty, 24 hours can be empty (So it means no record for that month for that location) and if the other months don't have a morning value inside; than it will take the average of 24 hours of other months and calculate it with a value as 0,2 for example and put it in the new table as July Morning.

The thing is I want to do it in a new table for not modifying the original entries. You said it can be done by summary fields which I have never done before. But I can do it if you describe me how to.

• ###### 5. Re: Trying to create table for filling the empty data and not modifying them. But the calculation...

I suggested summary fields, cautioning that I didn't have the full picture.

Sounds like you need a looping script that finds all the original data records for a given client and then loops through them, generating modified versions of these records. This can be done by creating records in the same table or a different table. Since the record structure is the same, it's possible to use the same table this might make it possible to combine the original and modified data in a single report where you can compare the values side by side. Of course that might not be needed.

it with a value as 0,2 for example

Does that mean you will compute 20% of the 24 hour figure for the same month (July 24?) and from the same record to use in place of the missing data point?

In not really clear on how you record both the 24 hour and the morning, afternoon, and night consumption figures. This could also be 12 records with 4 fields for each record or 48 records if you use a separate field for each figure--including the 24 hour total figure.

It's how you record the 24 hour figure that's unclear, given that you can have a 24 hour total but not all three of the periodic consumption totals.

• ###### 6. Re: Trying to create table for filling the empty data and not modifying them. But the calculation...

24 hour is a field because it is a different way of entering data. I understand summary can lead the way to success but I don't understand how summary can find the 7th month and enter the value of morning in my field as July_morning.

• ###### 7. Re: Trying to create table for filling the empty data and not modifying them. But the calculation...

24 hour is a field because it is a different way of entering data

A field in what record? Do you have 24 hour figures with each record (morning, afternoon and night)? I realize this is very clear to you, but I just don't quit have all the data here.

Summary fields compute an aggregate value (a total, an average, a maximum, etc...) for a set of records. This can be the found set, a sorted sub set or a set of related records--so the trick is to produce one of these 'sets' for just a specified date. Layout and table design are key and I just don't have all the details here yet.

• ###### 8. Re: Trying to create table for filling the empty data and not modifying them. But the calculation...

The thing is I changed my perspective and solve it as validating the empty fields at the location. So it is solved thank you.