Number format

[cmutombo]

Hi Communauty, 

I need help :

I have import an extraction from SAP to use on Qlik.

But, colonne suppose to have amounts is on format :

POSITIVE AMOUNTS :  1.500,00

NEGATIVE AMOUNTS : 1.500,00-

I can not  use NEGATIVE AMOUNTS with sign (-) at the end.

I need to change at correct format. 

Some one can assist ?

[Frank_Hartmann]

Load *, if(right(Value,1)='-','-'&purgechar(Value,'-'),Value) as NewValue;
Load * Inline [
	Value
	1500
	1500- ];

[cmutombo]

Thank you Frank for your reply.

But,  I want to change all column... not only two value

[Frank_Hartmann]

//Preceeding Load
Load *, if(right([SAP Timestamp Field],1)='-','-'&purgechar([SAP Timestamp Field],'-'),[SAP Timestamp Field]) as [New SAP Timestamp Field];

//and then load your Table with your original SAP Timestamp
Load 
...
[SAP Timestamp Field], 
...
FROM YourSourcetable;

[cmutombo]

Hi Frank_Hartmann

Thanks again for your support. Here below scripting of my load file (is a htm file)

Column with negative sign at right is "Amount"

Could you tell me where I should put what you are saying : 

LOAD
"G/L Acct",
"Pstng Date",
DocumentNo,
"Doc.Header Text",
"Text",
Text1,
Text2,
Text3,
Amount,
"Type",
"D/C",
Period,
"Custm Type",
Segment,
"Call Or/Dt",
"Tp Srv- HL",
User
FROM [lib://My connection (vodacomdrc_cmutombo)/SAP GUI/ZFIN_FI_CO_COMP.htm]
(html, codepage is 28591, embedded labels, table is @1);

[Frank_Hartmann]

LOAD *, 
if(right(Amount,1)='-','-'&purgechar(Amount,'-'),Amount) as NewAmount;
LOAD
"G/L Acct",
"Pstng Date",
DocumentNo,
"Doc.Header Text",
"Text",
Text1,
Text2,
Text3,
Amount,
"Type",
"D/C",
Period,
"Custm Type",
Segment,
"Call Or/Dt",
"Tp Srv- HL",
User
FROM [lib://My connection (vodacomdrc_cmutombo)/SAP GUI/ZFIN_FI_CO_COMP.htm]
(html, codepage is 28591, embedded labels, table is @1);

Then there should be a new field "NewAmount" in your Table with the correct formatting

[cmutombo]

THANK YOU VEEEERRRYYYYYYY MUCHHHHHHHHHH  !!!!!!!  Frank_Hartmann

You just got me out of several hours of research

😀😀😀😀

[Frank_Hartmann]

glad i could help you.

please close this thread by marking the correct answer.

[cmutombo]

Hi Frank_Hartmann

Load *, if(right([Amount],1)='-',
'-'&purgechar([Amount],'-'),
[Amount]) as Amount_    IS FINE

my negative signs are now on Left,

BUT,  Amount still remain on not correct format to summarize for ex :

I do : 

1. Replace(Amount_,' . ', ' ') 

2. Replace(Amount_,' , ',' . ') 

Loading Data is an Error.

Have some syntax to correct format directly ? 

Thanks in advance  

Load *, if(right([Amount],1)='-',
'-'&purgechar([Amount],'-'),
[Amount]) as Amount_

[Frank_Hartmann]

try this:

LOAD
"G/L Acct",
"Pstng Date",
DocumentNo,
"Doc.Header Text",
"Text",
Text1,
Text2,
Text3,
if(right(Amount,1)='-','-'&purgechar(Amount,'-'),Amount) as Amount,
"Type",
"D/C",
Period,
"Custm Type",
Segment,
"Call Or/Dt",
"Tp Srv- HL",
User
FROM [lib://My connection (vodacomdrc_cmutombo)/SAP GUI/ZFIN_FI_CO_COMP.htm]
(html, codepage is 28591, embedded labels, table is @1);