Recalculate column values based on column name

[Michiel_QV_Fan]

Hi,

I have multiple QVD's. These QVD's have the sames structure but different column names:

TempC and TempF (Temperature Celsius and Temperature Fahrenheit)

I want to harmonize these columns to be loaded in my application.

How can I recalculate the TempF column to celsius when the column is named TempF (obviously a TempC doens't need to be recalculated )

I tried this if(TempF= TempF, ((tempF-32) * (5/9)), TempC) as TempC,

but that gives an error because TempC cannot be found.

Thanks for your help

[Anonymous]

I don't see need for "IF" - just use expression based on TempF and call it TempC.

[Michiel_QV_Fan]

Michael, thanks.

I want to load all the different QVD's in one app. If the load encounters a TempF is should be recalcuated, if this column is TempC it should use it without calculation. Therefore 1 table is created with all data in celsius.

Therefor the If statement.

[Anonymous]

That's what I thoght.  This is my understanding.

QVD 1 has TempC, and QVD2 has TempF.  You load all this in one logical table:

Table:

LOAD

....

TempC,

...

FROM QVD1...;

CONCATENATE (Table) LOAD

....

(TempF-32)*5/9 as TempC,

...

FROM QVD2...;

[Michiel_QV_Fan]

Hi Michael,

Yes that's true.

However. I have more than 2 QVD's and I cannot determine which one has the C or F.

And, I cannot do this manually for all QVD's.

Thanks for your help

[Anonymous · Accepted solution]

OK, in this case it is still possible to do something.  Take a look at the error variables.  You can set ErrorMode=0, so the script continues to run in case of error.  Also, check variable ScripError in the script - if Script Error=11, that means that field not found.  So, it may look somewhat like this:
...
SET ErrorMode=0;
FieldCheck:

First 1     // no need to load more than one - just to see if th field exists
LOAD TempF FROM QVD1.qvd (qvd);

IF ScriptError=11 THEN

// it was error, use TempC
Table:
LOAD
...
TempC,
...
FROM QVD1.qvd (qvd);
ELSE

// TempF exists
Table:
LOAD
...
(TempF-32)*5/9 as TempC,
...
FROM QVD1.qvd (qvd);
ENDIF

DROP TABLE FieldCheck;

SET ScriptError=0;          // not sure it works, maybe it should be done differently
...
And the same for each QVD...

[Michiel_QV_Fan]

Michael, thanks.

Your answer works as you designed it.

I only get data when the field is TempF. Not when the field is TempC (this happens when I use *.qvd in the load statement.).

I suspect I could solve this with a for each next statement, but then I have to name all QVD in the comma separated list .......

The next step is to find out how I can load all QVD's, containing 12 columns of data, in 1 statement.