Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 !
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;
Perfect solution !
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.
Instead of using NUM() use NUM#() as below
NUM#(Replace(Replace(col1, '(', '-'), ')', '')) as col1
this will work fine.
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",
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 !
Perfect! That worked, thank you so much!!
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