Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
Load *, if(right(Value,1)='-','-'&purgechar(Value,'-'),Value) as NewValue;
Load * Inline [
Value
1500
1500- ];
Thank you Frank for your reply.
But, I want to change all column... not only two value
//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;
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);
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
THANK YOU VEEEERRRYYYYYYY MUCHHHHHHHHHH !!!!!!! Frank_Hartmann
You just got me out of several hours of research
😀😀😀😀
glad i could help you.
please close this thread by marking the correct answer.
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_
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);