Dear visitor, welcome to Palo Community Forum.
If this is your first visit here, please read the Help. It explains in detail how this page works.
To use all features of this page, you should consider registering.
Please use the registration form, to register here or read more information about the registration process.
If you are already registered, please login here.
Change the format of the number
Hello all,
I search how to change the format of the number. Example : A price is 3,100.05
And I want to view it in the format : 3.100,05
Someone know how I can do this?
Thank you for answering.
Nicolas.
You can use regualar expression to clean your data.
Hier some examples in Perl:
======================================================
Wir wenden regular Expressions auf eine Datei an:
perl -pi.bak -e "s/^.+E_A.+\n//" Plan2008vv.TXT
oder
perl -pi.bak -e "s/E_V2004/E_V2008/" Plan2004.TXT
perl -pi.bak -e "s/(0+\d+),000$/ $1/" iblkop
perl -pi.bak -e "s/(\d\d)000$/000$1/" iblkopneuer
perl -pi.bak -e "s/00000(\d\d),000$/ 00000000$1/" idibl
perl -pi.bak -e "s/\t-?\d+.*$/\t0/" idibl
Arme-Leute-Splashen:
perl -pi.bak -e "s/(.+\t.+\t.+\t.+)\t.+/$1\t0/" alles2004.TXT
Tabs in die erste Spalte:
perl -pi.bak -e "s/^(.+)$/\t$1/" StabV2005ist.TXT
Dezimalpunkte durch Kommata in einer Spalte ersetzen:
perl -pi.bak -e "s/(\t-*\d+)\.(\d{4})/$1,$2/" kart.txt
mhhh.
thank's for the answer.
But,
I don't understand very well what do meen by "use regular expression to clean your data".
Why do I must clean my data?
Where can I (in Palo ETL) put regular expression?
Thank's.
Nicolas.
A regular expression can be used e.g. to replace "," with ".": In Palo ETL this can be done with Transformer "Replace". With the next release there will also be a specific Transformer "NumberFormat" to change the format of numbers.
Hi all,
I also wanted to know if I can format the number of my measure tag (instead of 12,000.00 -> 12.000 or 12000, no need of floating-point number)
<measures name="Measures_Contract" aggregate="count"> <measure name="Participants"> <input nameref="ParticipantID"/> </measure> </measures>
Is there any way to do that with a rule in an etl project?
thanks
sivgin
sivgin
Yes, like this:
<transformer name="LineCost_trans" type="Replace">
<inputs>
<input nameref="LineCost" />
</inputs>
<parameters>
<parameter name="regexp">,</parameter>
<parameter name="replacement">.</parameter>
</parameters>
</transformer>
Thanks for your quick reply!
Sorry but this is not what I am searching for.
I am counting IDs.. to know the amout of Participants within the measure tag... a replacement transformer is not working for that.
Even if I am putting a new column in my sql and do the sum in measure tag, Palo shows floating-point numbers in rcp and web-palo.
select
x,
y,
1
from ...
Is the data stored in floating-point numbers or is it more a question of visualisation?
Cheers
sivgin
sivgin
Hello,
It comes always always as floating point because of the palo client (excel for example), but not from ETL. If you are using excel, you can change this in palostyles.xls.
Thank you for the answer...
