topic Recalculate column values based on column name in QlikView

Recalculate column values based on column name

Michiel_QV_Fan — Mon, 14 Nov 2011 10:45:23 GMT

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

Recalculate column values based on column name

Anonymous — Mon, 14 Nov 2011 11:34:54 GMT

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

Recalculate column values based on column name

Michiel_QV_Fan — Mon, 14 Nov 2011 11:38:12 GMT

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.

Recalculate column values based on column name

Anonymous — Mon, 14 Nov 2011 13:19:24 GMT

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...;

Recalculate column values based on column name

Michiel_QV_Fan — Mon, 14 Nov 2011 13:27:10 GMT

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

Recalculate column values based on column name

Anonymous — Mon, 14 Nov 2011 15:29:11 GMT

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...

Recalculate column values based on column name

Michiel_QV_Fan — Tue, 15 Nov 2011 08:30:57 GMT

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.