topic Re: Number format in QlikView

Number format

cmutombo — Fri, 26 Mar 2021 10:35:32 GMT

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 ?

Re: Number format

Frank_Hartmann — Fri, 26 Mar 2021 12:41:06 GMT

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

Re: Number format

cmutombo — Fri, 26 Mar 2021 12:53:18 GMT

Thank you Frank for your reply.

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

Re: Number format

Frank_Hartmann — Fri, 26 Mar 2021 13:10:26 GMT

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

Re: Number format

cmutombo — Fri, 26 Mar 2021 13:23:56 GMT

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

Re: Number format

Frank_Hartmann — Fri, 26 Mar 2021 13:29:01 GMT

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

Re: Number format

cmutombo — Fri, 26 Mar 2021 13:31:54 GMT

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

You just got me out of several hours of research

😀😀😀😀

Re: Number format

Frank_Hartmann — Fri, 26 Mar 2021 13:45:36 GMT

glad i could help you.

please close this thread by marking the correct answer.

Re: Number format

cmutombo — Fri, 26 Mar 2021 14:09:15 GMT

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_

Re: Number format

Frank_Hartmann — Fri, 26 Mar 2021 14:22:26 GMT

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

Re: Number format

cmutombo — Fri, 26 Mar 2021 15:06:37 GMT

Hi Frank_Hartmann

Here below the CORRECT ONE. THANKS A LOT FROM YOU !

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

Replace(if(right([Amount],1)='-',
'-'&purgechar([Amount],'-'),
[Amount]),'.',''),

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

Re: Number format

cmutombo — Fri, 26 Mar 2021 15:07:14 GMT

CORRECT ANSWER !

Re: Number format

cmutombo — Fri, 26 Mar 2021 16:43:55 GMT

Hi Frank,

Could you help with this formula... Something is wrong.

The result don't consider that i am suming All transaction of current month

=sum({$<[MonthName(Date)]= {"$(=MonthName(Today()))"}>}Amount_)