topic Re: Load string as number in QlikView

Load string as number

markp201 — Wed, 31 May 2017 18:08:39 GMT

Amount
0.00 800.00 1,203.12 901.00 235.00

Amount

0.00

800.00

1,203.12

901.00

235.00

I applied num#(Amount) as Amount and get

Amount
? ? ? ? ?

Amount

1. Why right-justified if its not a number?

2. Why the ? mark

Re: Load string as number

sunny_talwar — Wed, 31 May 2017 18:14:52 GMT

Try this may be

Num#(Amount, '#,##0.00')

Re: Load string as number

markp201 — Wed, 31 May 2017 18:37:06 GMT

Same result. Returns ?

Re: Load string as number

Anonymous — Wed, 31 May 2017 18:37:55 GMT

are your system variables set like that?

SET ThousandSep=',';

SET DecimalSep='.';

Re: Load string as number

sunny_talwar — Wed, 31 May 2017 18:38:27 GMT

Would you be able to share a sample?

Re: Load string as number

markp201 — Wed, 31 May 2017 18:44:08 GMT

No change.

I've used num#(field) in a number of places and it works fine. I don't why this is different.

Why the ? I would expect null.

Re: Load string as number

markp201 — Wed, 31 May 2017 18:46:02 GMT

This is a very simple script.

LOAD num#(field) as field.

In other places it works fine

Re: Load string as number

sunny_talwar — Wed, 31 May 2017 18:48:25 GMT

This is a document specific issue because when I try this.... it works for me

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Table:

LOAD *,

num#(Amount) as New_Amount;

LOAD * INLINE [

Amount

0.00

800.00

"1,203.12"

901.00

235.00

];

Re: Load string as number

its_anandrjs — Wed, 31 May 2017 18:49:34 GMT

Did you read the data from any excel file. If possible share sample data.

Regards

Anand

Re: Load string as number

settu_periasamy — Wed, 31 May 2017 18:49:54 GMT

not sure. May be check this..

Multiple Question mark in list box

Re: Load string as number

Anonymous — Wed, 31 May 2017 18:54:46 GMT

did you try num(num#(FIELD)) ?

Re: Load string as number

markp201 — Wed, 31 May 2017 19:08:14 GMT

Same result. Apparently Qlik can get confused about the formatting but this is straight-forward. What's really weird is the ? appears for filters and dimensions but not for measures. The measure looks fine but the user needs this in a table without aggregation.

Re: Load string as number

markp201 — Wed, 31 May 2017 19:11:43 GMT

Screen shot

Re: Load string as number

sunny_talwar — Wed, 31 May 2017 19:13:02 GMT

How about if you just use Num(Amount) instead of Num#(Amount)?

Re: Load string as number

markp201 — Wed, 31 May 2017 19:44:54 GMT

Ok, this makes no sense but it works

Originally, pass 1

- load from a resident table

- one field uses applymap

- problem was on num#(field) as field.

On a guess, I resolved this with the following

Pass 1

- load from a resident table

- one field uses applymap

- problem solved by using a new field name - field as newfield.

Pass 2 restores the original field name

- loads from pass 1

- num#(newfield) as field

It's a small table - only mildly annoying.

Please just be happy it's solved - not looking for suggestions. 15 minutes till I can leave for the day.