Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NemoAndStitch22
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!

Labels (10)
1 Solution

Accepted Solutions
vijayaganesh_s
Partner - Contributor III
Partner - Contributor III

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

vijayaganesh_s
Partner - Contributor III
Partner - Contributor III

Perfect solution !

NemoAndStitch22
Contributor III
Contributor III
Author

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.

 

vijayaganesh_s
Partner - Contributor III
Partner - Contributor III

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

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

this will work fine.

NemoAndStitch22
Contributor III
Contributor III
Author

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",

vijayaganesh_s
Partner - Contributor III
Partner - Contributor III

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 !

NemoAndStitch22
Contributor III
Contributor III
Author

Perfect! That worked, thank you so much!!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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