topic Re: Number Formatting Issue in Script in QlikView

Number Formatting Issue in Script

crystles — Mon, 22 May 2017 17:47:45 GMT

I am creating a document that has a table which is replacing an excel report that a department currently gets daily.

I need to have the table in Qlikview, formatted to the way they are used to seeing it in the daily Excel report and I am trying to accomplish this formatting in the script so I do not have to repeat it in each table it is used in, in the Qlikview document

The problem is, I can't seem to match the format. See table below for example.

The values need to only go out to 3 spaces after the decimal point, but ONLY if it has values for those three spaces (see how Items "ACB" and "AEB" have values after the decimal but not 3 spaces out so it shortens it to either 2 or 1 spaces)

Then if the value is 0, it just needs to be "0", not "0.000".

I tried using this format below but it did not give me the results I wanted.

Num(ItemReq, '###0.###')

Is this even possible in Qlikview?

Item	Item Requirements
ABC	2347
ABD	0
ADE	1000.523
ACB	75.48
AEB	158.2

Re: Number Formatting Issue in Script

Anonymous — Mon, 22 May 2017 17:59:40 GMT

perhaps like that?

SET ThousandSep=',';
SET DecimalSep='.';

LOAD num(Round(F1,0.001));

LOAD * INLINE [
F1
1213.11
2347.3
0
1000.52322
75.4823
158.221
];

Re: Number Formatting Issue in Script

felipedl — Mon, 22 May 2017 18:00:55 GMT

Hi Crystle,

You can use the frac() function to check if the number has a fraction portion and then do the formatting.

See the attached file to see if it helps you.

Regards,

Felipe.

Re: Number Formatting Issue in Script

crystles — Mon, 22 May 2017 18:34:45 GMT

I see how you used the fraction function... but it changed the numbers to the format that I'm already getting?

I want them to look like they do in the table example, without the "0"s after the decimal?

Instead of "158.200", I want it to be "158.2" only.

I can try and use the fraction function to accomplish this though.

Re: Number Formatting Issue in Script

crystles — Mon, 22 May 2017 18:35:42 GMT

I tried this but I cannot seem to get it to work? It gave me some very strange results too, it removed the decimal entirely and just gave one long number?

Re: Number Formatting Issue in Script

Anonymous — Mon, 22 May 2017 18:37:22 GMT

then please upload some sample data in Excel Format, so that I can see the strange results

Re: Number Formatting Issue in Script

felipedl — Mon, 22 May 2017 18:42:30 GMT

Hi,

Just replace the num() formatting for something like (attached file has an example):

round([Item Requirements],0.001), where the 0.001 is the offset as to how much rounding you want.

Felipe.

Re: Number Formatting Issue in Script

Anonymous — Mon, 22 May 2017 19:39:47 GMT

You really shouldn't have to do anything special.

Format as number without specifying precision.

Are you selected a decimal place on the number tab?

Re: Number Formatting Issue in Script

Tue, 23 May 2017 22:05:31 GMT

It might not be the most efficient solution but you can try something like this for the field in the script containing the number:

Table1:
Load * Inline
[Record,Val
A,10
B,121.11
C,50.34456
D,1.1
E,0
F,532
];

Table2:
NoConcatenate
Load Record,
If(Right(Text(Round(Val,0.001)),1)=0,
If(Right(Text(Round(Val,0.01)),1)=0,
If(Right(Text(Round(Val,0.1)),1)=0,Round(Val,1),
Round(Val,0.1)),
Round(Val,0.01)),
Round(Val,0.001)) as Val
Resident Table1;

Drop Table Table1;

Re: Number Formatting Issue in Script

ahaahaaha — Wed, 24 May 2017 08:02:41 GMT

Hi Crystle,

If I understand you correctly, you will only need to apply function Num(). I used various variant of numbers in the two fields (number field and text field) Excel file (see attached files) - point and comma as delimiters, different of digits.

Result

In attached files source data and example for QlikView/Qlik Sence.

Regards,

Andrey