topic Changing data on LOAD in QlikView

Changing data on LOAD

Sun, 20 Nov 2011 20:44:10 GMT

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.

Changing data on LOAD

Mon, 21 Nov 2011 02:17:18 GMT

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

Should work fine.

Changing data on LOAD

Mon, 21 Nov 2011 08:22:17 GMT

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?

Changing data on LOAD

jagannalla — Mon, 21 Nov 2011 08:31:11 GMT

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.

Changing data on LOAD

Mon, 21 Nov 2011 09:13:49 GMT

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