Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Qliksense Data Load Negatives

Hiya, 

 

I'm currently loading in my data and in my txt file the negatives are shown in Brackets.

This doesn't get read as numbers by Qliksense.

I can't change the load data as this is how it comes out the system. 

Do you know how I can script it to make it recognise them as negative numbers?

Thanks!

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Directly replace Col1 by the field which you want to change.

Example if you want the negative data in Stats Value just use 

"Processed Sales Data":
LOAD

MakeDate(Mid(FileBaseName(),39,4),mid(FileBaseName(),43,2)) as Date,
Mid(FileBaseName(),39,4) as Year,
Mid(FileBaseName(),43,2) as Month,
"Effective Date",
num#(Replace(Replace([Stats Value], '(', '-'), ')', '')) as [Stats Value],
"Change User",
"Change Date",

Etc.,

No mapping is required !

View solution in original post

8 Replies
Highlighted
Creator III
Creator III

Hey,

this one should work:

Table:
load * inline [
ID, col1

1, (7)

2, 5

3, (6)

4, 8
];

NoConcatenate
Table2:
Load
ID,
num(Replace(Replace(col1, '(', '-'), ')', '')) as col1
Resident Table;
Drop Table Table;

Highlighted
Partner
Partner

Perfect solution !

Highlighted
Contributor III
Contributor III

Thanks for this, but it's not working for me, it's just giving me the -7 and -6 as minuses and nothing else. 

I copy and pasted the exact script you gave and it's not doing anything.

Should I be changing one of the field names to be one that's in my script?

 

I can't work out how to make it work.

 

Highlighted
Partner
Partner

Instead of using NUM() use NUM#() as below

NUM#(Replace(Replace(col1, '(', '-'), ')', '')) as col1

this will work fine.

Highlighted
Contributor III
Contributor III

It's still not made a difference to my data... This is what I've got. 

Stats value is the field that I need this to work on, as previously asked should I be somehow mapping the table to my  data load file?

Table:
load * inline [
ID, col1

1, (7)

2, 5

3, (6)

4, 8
];

NoConcatenate
Table2:
Load
ID,
num#(Replace(Replace(col1, '(', '-'), ')', '')) as Col1
Resident Table;
Drop Table Table;

 


"Processed Sales Data":
LOAD

MakeDate(Mid(FileBaseName(),39,4),mid(FileBaseName(),43,2)) as Date,
Mid(FileBaseName(),39,4) as Year,
Mid(FileBaseName(),43,2) as Month,

"Effective Date",
"Stats Value",
"Change User",
"Change Date",

Highlighted
Partner
Partner

Directly replace Col1 by the field which you want to change.

Example if you want the negative data in Stats Value just use 

"Processed Sales Data":
LOAD

MakeDate(Mid(FileBaseName(),39,4),mid(FileBaseName(),43,2)) as Date,
Mid(FileBaseName(),39,4) as Year,
Mid(FileBaseName(),43,2) as Month,
"Effective Date",
num#(Replace(Replace([Stats Value], '(', '-'), ')', '')) as [Stats Value],
"Change User",
"Change Date",

Etc.,

No mapping is required !

View solution in original post

Highlighted
Contributor III
Contributor III

Perfect! That worked, thank you so much!!

Highlighted
MVP & Luminary
MVP & Luminary

Another alternative is to use the num#() format pattern to indicate that () is negative:

LOAD
  num#(x, '0;(0)') as x
Inline [
x
4
(5)
]
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com