Can't do a sum() on the text field...

[#1]

Hi All,

I've got an issue with a text file I'm importing into my QVW.

Imported the text file in the script; all went well, data is available...

or is it?

Transactions:

LOAD @1 as [Eigen Rekening],

     @2 as Valuta,

     MakeDate(Left(@3,4), Mid(@3,5,2),Mid(@3,7,2)) as [Tranactie Datum],

     @4 as [C-D],

//     Num#(@5) as Bedrag,

     @5 as Bedrag,

     @6 as [Tegen Rekening],

     @7 as Geadresseerde,

     MakeDate(Left(@8,4), Mid(@8,5,2),Mid(@8,7,2)) as Datum,

     @9 as Soort,

     @11 as Omschrijving#1,

     @12 as Omschrijving#2,

     @13 as Omschrijving#3,

     @14 as Omschrijving#4,

     1            as Count#

FROM

(txt, codepage is 1252, explicit labels, delimiter is ',', msq);

The field Bedrag is must be a numerical field, but I can't seen to change it from text to numerical

I can't do a sum() or Avg() becouse it is a text field.

Please help, tnx a lot

[m_woolf]

What values are in Bedrag?

[#3]

currency in Euro.

[MayilVahanan]

HI

     Use Num(@5) as Bedrag

     Instead Num#(@5) as Bedrag

Hope it helps

[#5]

Hi Mayil,

I've done that, but it did not work at all.

I got all empty value's

Any other suggestions are highly apriciated.

[MayilVahanan]

HI

     Can you post the sample input file ?

[#7]

HI Mayil,

Here is a sample imput file.

I scrambled the date a bit since it is confedential.

14xx3x08x

EUR

20110601

D

.03

0

Debetrente

20110606

db

Periode 01-05-2011 t/m 31-05-201

14xx3x08x

EUR

20110606

D

12.94

0

XXXXXXXXX XXXXXX XXXXXXX

20110606

ba

Betaalautomaat 14:10 pasnr. 006

14xx08xx3

EUR

20110907

D

32.00

943926343

XXXXXXXXXXX XXXXXXXX

20110907

bg

XXXXXXox 2-9-2011

groetjes en bedankt

14xx08xx3

EUR

20110911

D

70.00

0

ING BANK XXXXXXX 6x3x AT

20110912

ga

Geldautomaat 13:32 pasnr. 005

18xx1xx40

EUR

20120903

D

100.00

230108377

xExxL LxxVExZ

20120903

ma

BETALINGSKENM.  286858823360195

WINxxX98          POL xxxxxxxxxx

1 SEP / 1 OKT

18xx1xx40

EUR

20120903

D

125.71

230108377

xExxL LxxVExZ

20120903

ma

BETALINGSKENM.  286858853360195

WINxxX98          POL xxxxxxxxxx

1 SEP / 1 OKT

[jvitantonio]

I can sum it with no problem. Check out the app.