Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

crystles
Contributor III

Number Formatting Issue in Script

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?

ItemItem Requirements
ABC2347
ABD0
ADE

1000.523

ACB75.48
AEB158.2
9 Replies
roharoha
Valued Contributor III

Re: Number Formatting Issue in Script

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
]
;

felipedl
Valued Contributor III

Re: Number Formatting Issue in Script

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.

crystles
Contributor III

Re: Number Formatting Issue in Script

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.

crystles
Contributor III

Re: Number Formatting Issue in Script

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?

roharoha
Valued Contributor III

Re: Number Formatting Issue in Script

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

felipedl
Valued Contributor III

Re: Number Formatting Issue in Script

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.

atkinsow
Valued Contributor II

Re: Number Formatting Issue in Script

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?

Not applicable

Re: Number Formatting Issue in Script

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;

ahaahaaha
Honored Contributor

Re: Number Formatting Issue in Script

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

1.jpg

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

Regards,

Andrey

Community Browser