Changing data on LOAD

[#1]

Hi friends,

I have a Excel table with financial data and following structure (and format):

Xdate        Xvalue      Xtype     ...

11.11.2011   12345,67    Debet    ...

11.11.2011   7654,32     Kredit   ...

...

I would want to load this data, but changing data on load according by rule:

if attribute "Xtype" is equals to: Debet, value in attribute "Xvalue" has to be equal to: -12345,67 (change value to minus)

if attribute "Xtype" is equals to: Kredit, value stays the same (equals to): 7654,32

I try to use following load:

LOAD

    Xdate,

    Xvalue,

    Xtype,

    if(Xtype='Kredit',Xvalue,Xvalue=(-1)*Xvalue)) AS UpdatedValue

FROM (ooxml, no labels);

but in list "UpdateValue" I have only value=0 (nothing else).

please help me.

[#2]

IF (Xtype = 'Kredit', Xvalue, -1 * Xvalue) As UpdatedValue

Should work fine.

[#3]

hi Sarcich,

this command in load was updated all values (see next screenshot):

I need updated (UpdatedValues=(-1)*(XValue)) only values which associate XType equals 'Debet').

PS:

LOAD XDate, Value, XType,

    if (E='Kredit',C,-1*C) AS UpdatedValue

FROM (ooxml, no labels);

please help me, where is problem?

[jagannalla]

Actually B.Sarcich code should work. Ok can you upload a sample data file.

Give your file with load fields value,XType. It helps us to work on this.

[#5]

I'm sorry, problem was in wrong string format (leading spaces). Mea culpa!

Right solution is:

LOAD Xdate,

     Value,

     Xtype,

     if(Trim(Xtype)='Kredit',Value,-1*Value) as UpdatedValue   

FROM C:\Temp\data.xlsx (ooxml, embedded labels);

missing "Trim" function

thanks for all