Perform calculation in Load Script on a value if other criteria is met

[Sam_Thomas]

Hi

I have a small data set that includes a site, datetime and value. All of my values are hourly values. These value represent a % figure of how full a container is. 

For one site, the value is the actual value (metres). So, I will need to convert these values into percentages based on the value and the overall size of the container. I.e. value/total size * 100 (%). 

In the load script how can I write: 

IF [SITE] = SITE 4, (VALUE) / (TOTAL SIZE)*100

Thanks. 

[MT4T]

Hi,

It should be:

IF([SITE]='SITE 4',([VALUE]/[TOTAL SIZE])*100)      as [new field name],

Regarding 'SITE 4' i assume this is one of string contained withins [SITE] field.

[Sam_Thomas]

Hi thanks for the suggestion. Maybe I haven't done this correctly but it just transforms all the sitenames into the calculated figures. This is how I have added your code: 

LOAD
IF([Site]='SITE4',([Avg_Value]/8)*100) as [Site],
Tag,
DateTimeHour,
Avg_Value

FROM [lib://SourceDataFiles/Data.qvx]
(qvx);

[Sam_Thomas]

I tried 

LOAD
Site,
piTag,
DateTimeHour,
IF([Site]='SITE4',([Avg_Value]/8)*100) as [Avg_Value]

This gives me the correct data for SITE4 but doesn't show any of the other values for the other sites. 

Tried

LOAD
Site,
piTag,
DateTimeHour,
IF([Site]='SITE4',([Avg_Value]/8)*100), [Avg_Value] as [Avg_Value]

And this gives me all the values for all sites but doesn't convert the SITE4 data as per the calc. 

[MT4T]

Hi,

Yes of course, you must use different [new field name].

When you use the same as you have in source table the source will be replaced with calculated one.

In below example your result will look like:

IF([Site]='SITE4',([Avg_Value]/8)*100) as [CalculatedSite],   - one additional column will appear

Site,    - old column will remain 

Tag,
DateTimeHour,
Avg_Value

FROM [lib://SourceDataFiles/Data.qvx]
(qvx);

[MT4T · Accepted solution]

Yes, because this is only example with "IF YES" condition. There was no suggestion within your question what to do "IF NO"

IF [SITE] = SITE 4, (VALUE) / (TOTAL SIZE)*100

When you want another calculation in this place you could replace my sample calculation marked with bold with any other.

IF([Site]='SITE4',([Avg_Value]/8)*100,[Avg_Value]/5*100) as [Site],

[Sam_Thomas]

Apologies for the confusion. 

But I have it working now anyway 

LOAD
Site,
piTag,
DateTimeHour,
IF([Site]='PEACEHAVEN',([Avg_Value]/8)*100, [Avg_Value]) as [Avg_Value]

Many thanks for your help.