Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
crystles
Partner - Creator III
Partner - Creator 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
Anonymous
Not applicable

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
Partner - Specialist III
Partner - Specialist III

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
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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?

Anonymous
Not applicable

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

felipedl
Partner - Specialist III
Partner - Specialist III

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.

Anonymous
Not applicable

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

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
Partner - Master
Partner - Master

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