topic Re: Qliksense Data Load Negatives in App Development

Qliksense Data Load Negatives

NemoAndStitch22 — Sat, 16 Nov 2024 19:36:18 GMT

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!

Re: Qliksense Data Load Negatives

y_grynechko — Fri, 22 Nov 2019 16:47:34 GMT

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;

Re: Qliksense Data Load Negatives

vijayaganesh_s — Fri, 22 Nov 2019 17:36:44 GMT

Perfect solution !

Re: Qliksense Data Load Negatives

NemoAndStitch22 — Sat, 23 Nov 2019 08:21:45 GMT

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.

Re: Qliksense Data Load Negatives

vijayaganesh_s — Sat, 23 Nov 2019 10:14:02 GMT

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

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

this will work fine.

Re: Qliksense Data Load Negatives

NemoAndStitch22 — Mon, 25 Nov 2019 11:48:34 GMT

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

Re: Qliksense Data Load Negatives

vijayaganesh_s — Mon, 25 Nov 2019 11:55:01 GMT

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 !

Re: Qliksense Data Load Negatives

NemoAndStitch22 — Mon, 25 Nov 2019 11:59:08 GMT

Perfect! That worked, thank you so much!!

Re: Qliksense Data Load Negatives

rwunderlich — Thu, 28 Nov 2019 18:07:09 GMT

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