topic Import text as a number in QlikView

Import text as a number

Tue, 13 Apr 2010 16:11:06 GMT

Hi all,

I'm trying to import data from a large text file. It has fixed width columns so I've gone through the import wizard and set up the widths and relevant column names etc. Trouble is, it seems to be importing the Balance field as text.

So I took a look at the import again and saw the facility to specify data type. So I re-did it and selected Real for the Balance field. This seemed to make that field disappear from the wizard, which looked a bit odd.

But then i could see in the script QV has placed an "R" beside the relevant column width defnintion, so I now have something like this:


LOAD @1:4,
 @5:16,
 @17:70,
 @71:82,
 @83:104,
 @105:116,
 @117:138,
 @139:151,
 @152:181,
 @182:189,
 @190:200,
 @201:205 as Currency,
 @206:224R as Balance,
 @225:229 as BaseCurrency,
 @230:248 as BaseBalance,
 @249:253 as SIHCurrency,
 @254:272 as SIHBalance,
 @273:n
FROM
\\ldndata05\userdata$\macpheto\Documents\Project\Ledger\SAPFiles\TBs\Zplatod_09022010v2.txt
(fix, codepage is 1252);

You can see the "R" in the Balance field. However, this just errors when it runs, with the message:

Field not found - <@206:224R>

Any ideas? I'm really stumped and can't find anything in the help. And I can't do much with the Balance field if it is imported as text...

TIA,

Tony

Import text as a number

Miguel_Angel_Baeyens — Tue, 13 Apr 2010 16:29:43 GMT

Hello Tony,

Have you tried

Num(@206:224) AS Balance

Import text as a number

Tue, 13 Apr 2010 17:41:24 GMT

Hi Miguel,

Yes, I tried that, with confusing results.... I changed my import script to:


...
@206:224 AS TextBalance,
Num(@206:224) AS Balance,
...

and it seems to convert some values, but not others. So now I'm seeing:

Currency	TextBalance	Balance
KWD	9328.91	9328.91
TND	24576.39	24576.39

which is great, but I'm also seeing:

Currency	TextBalance	Balance
EUR	000000001038327.27-
EUR	000000001038327.27
RUB	000000000066432.10-
RUB	000000162481698.09
RUB	000000222417753.69

where Balance is being calculated as null.

It looks like it's something to do with the TextBalance field, but I can't figure out any difference. Note - even though the first example is showing the TextBalance field as numbers in Excel all values in this field are actually preceded by zero's, as seen in the second example.

Weird... and infuriating!!!

Thanks for your help, any other suggestions?

Tony

Import text as a number

Anonymous — Tue, 13 Apr 2010 17:49:50 GMT

Tony,
If there are non-mumeric characters in the string, it will not be converted into number. Is there a possibility that there are non-printable chartacters there? You can try keepchar() function:
num(keepchar((@206:224), '0123456789.'))

Import text as a number

flavio_fazzano — Tue, 13 Apr 2010 18:34:31 GMT

Tony, have you verified that the Character "," (comma) is the rigth setting in ThousandSep=',' ?

Import text as a number

rwunderlich — Tue, 13 Apr 2010 19:45:22 GMT

Tony,

QV won't recognize trailing minus signs. You have to do a bit of script like:

if(right(@206:224 ,1) = '-',
0-left(text,len(@206:224 )-1),
@206:224
) as Balance

-Rob

Import text as a number

Wed, 14 Apr 2010 10:19:24 GMT

All,

Thanks for the suggestions, I'll work on these and give you an update later.....

Tony

Import text as a number

Thu, 15 Apr 2010 14:28:34 GMT

Hi all,

This is still puzzling me I'm afraid... any other suggestions would be much appreciated.

In the source file I have:



THB 000001075919563.06 USD
THB 000001909199021.60-USD
KWD 00000000000000.000 USD
KWD 00000000009328.910-USD

and I have now changed the script to treat the minus sign as a separate column, which I also use to calculate the actual Balance from the TextBalance field:


@201:205 as Currency,
@206:223 as TextBalance,
@224:224 as Sign,
If ( @224:224 = '-',
 0 - Num(@206:223),
 0 + Num(@206:223))
 as Balance,

but it still isn't converting most of the values into numbers:

Currency	TextBalance	Sign	Balance
THB	000001075919563.06
THB	000001909199021.60	-
KWD	0		0
KWD	9328.91	-	-9,329

Note - in QV the TextBalance columns are ALL preceded by a bunch of zero's. But the really interesting thing is that those values that are being treated as numbers all have 3 decimal places, as you can see in the text file above. So it seems as though QV is automatically converting the entries with 3 decimal places into a number anyway, as you can see from the right-aligned numeric values in TextBalance. But those entries with 2 decimal places it is treating as text and it is not converting them when I use the Num function.

I'm going to try adding a zero on the end of the 2dp values to see if Num works then, but any more sensible suggestions would be greatly appreciated.....

Tony

Import text as a number

d_pranskus — Thu, 15 Apr 2010 14:56:16 GMT

Would you attache some source file to test it?

Cheers

Darius

Import text as a number

Anonymous — Thu, 15 Apr 2010 15:05:15 GMT

Have you tried num(keepchar(...))?

Import text as a number

d_pranskus — Thu, 15 Apr 2010 15:08:07 GMT

Seems I managed to figure it out

Use following expression, when you load the data:

NUM(EVALUATE(@4:26), '##############') AS Amount

EVALUATE() to convert to number, NUM() to remove leading zeros

Cheers

Darius

Import text as a number

Thu, 15 Apr 2010 15:41:43 GMT

Hi Michael, Yes I tried that one. No change - there weren't any non-numeric characters in the field.

Thanks for the suggestion.

Tony

Import text as a number

Thu, 15 Apr 2010 15:43:11 GMT

Darius that's brilliant, works perfectly!

Thanks v much,

Tony

Import text as a number

d_pranskus — Thu, 15 Apr 2010 15:43:38 GMT

Did you try NUM(EVALUATE(@4:26), '##############') AS Amount as I suggested? It worked for me.

Cheers

DArius

Import text as a number

Thu, 15 Apr 2010 15:54:43 GMT

Yes Darius, it worked for me too. Thanks again.

Any idea why my original method - specifying the column as a Real number - didn't work? The fixed-width import wizard in QV seems a little buggy to me, do you have any experience with that?