topic Load text numbers in QlikView

Load text numbers

Wed, 25 Nov 2020 16:16:04 GMT

Hello good evening,

I'm trying to load sensor data, most of the errors i filtered out. But i run into one last problem.

DATA:

2016-02-05 00:04:16	424,000
2016-02-05 00:09:40	0,426
2016-02-05 00:15:01	0,426
2016-02-05 00:20:37	427,000
2016-02-05 00:25:46	427,000
2016-02-05 00:31:07	428,000
2016-02-05 00:36:39	428,000
2016-02-05 00:42:02	428,000
2016-02-05 00:47:20	429,000

Above it shows the data, most is displayed correctly. But 2 values are not displayed correctly.

In the database the values are stored as text i gues as text. The dots ( . ) got seen as thousand, but it are decimals, i corrected this in the top of the script.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

Then i noticed this didnt work and i loaded the field with the following load statement:

num(evaluate(history_value))

AS history_value,

After this the data got alot better, but now one last thing. in the table below you see how its stored in the database. the 0.426 should be 426.000 so the 0. should be removed in front.

DATA: in database

2016-02-05 00:04:16	424.000
2016-02-05 00:09:40	0.426
2016-02-05 00:15:01	0.426
2016-02-05 00:20:37	427.000
2016-02-05 00:25:46	427.000
2016-02-05 00:31:07	428.000
2016-02-05 00:36:39	428.000
2016-02-05 00:42:02	428.000
2016-02-05 00:47:20	429.000

The complete load script is as following:

Header 1
FIELDHISTORYDATE2016: LOAD field_typeid, field_id, history_setvalue, timestamp(history_timestamp) AS history_timestamp, history_userid, num(evaluate(history_value)) AS history_value, //Num(history_value) AS history_value, field_description, field_owner_id AS loc_node_id, field_setvalue, field_type, field_value from d:\QVD\FIELDHISTORYDATE2016.qvd(qvd);

Header 1

FIELDHISTORYDATE2016:

LOAD

field_typeid,

field_id,

history_setvalue,

timestamp(history_timestamp) AS history_timestamp,

history_userid,

num(evaluate(history_value)) AS history_value,

//Num(history_value) AS history_value,

field_description,

field_owner_id AS loc_node_id,

field_setvalue,

field_type,

field_value

from d:\QVD\FIELDHISTORYDATE2016.qvd(qvd);

Anyone got any suggestions how to fix this? Thanks in advance!

Re: Load text numbers

swuehl — Wed, 05 Oct 2016 22:21:06 GMT

Could you post some sample data (your input data file, if it is a text file or Excel file, or the QVD you are loading in your script code)?

Re: Load text numbers

Wed, 05 Oct 2016 22:52:42 GMT

The QVD file is around 3gb, so that abit much to share, but i can give the following information:

The QVD i created from a postgres database with the following:

FIELDHISTORYDATE2016:
SELECT
fieldhistory.field_id,
fieldhistory.setvalue             AS history_setvalue,
fieldhistory.timestamp            as history_timestamp,
fieldhistory.user_id       as history_userid,
fieldhistory.value         AS history_value,
field.description          as field_description,
field.fieldtype_id         AS field_typeid,
field.owner_id                    AS field_owner_id,
field.setvalue                    AS field_setvalue,
field.type                 AS field_type,
field.value                AS field_value
FROM field
LEFT JOIN fieldhistory ON field.id = fieldhistory.field_id

"field.fieldtype_id" != '2' AND
"field.fieldtype_id" != '33' AND // High Voltage Switch Output
              "field.fieldtype_id" != '7' AND // SwitchControlPosition
              "field.fieldtype_id" != '26' AND // SwitchedEnergy
              "field.fieldtype_id" != '27' AND // UnswitchedEnergy
              "field.fieldtype_id" != '29' AND // SwitchedPower
              "field.fieldtype_id" != '30' AND // UnswitchedPower
              "field.fieldtype_id" != '32' AND // SwitchControlMode
              "field.fieldtype_id" != '33' AND // High Voltage Switch Output
              "field.fieldtype_id" != '51' AND // Energy Meter
              "field.fieldtype_id" != '32' AND // LampEnergy
              "field.fieldtype_id" != '52'             // LampEnergy

;
Store FIELDHISTORYDATE2016 into d:\QVD\FIELDHISTORYDATE2015.qvd;

DB fieldhistory fields are like:

Column Type Not Null Default Constraints Actions Comment

field_id integer
NOT NULL
0 Browse Alter Privileges Drop

timestamp double precision
NOT NULL
Browse Alter Privileges Drop

value character varying(100) Browse Alter Privileges Drop

setvalue character varying(100) Browse Alter Privileges Drop

user_id integer
NOT NULL
Browse Alter Privileges Drop

data is stored like:

field_id	timestamp	value	setvalue	user_id
177894	41563.8906801273	20.746	NULL	0
177894	41563.8963386574	20.769	NULL	0
177894	41563.9029315625	20.799	NULL	0
177894	41563.9078352778	20.813	NULL	0
177894	41563.9133997685	0.421	NULL	0
177894	41563.9190640856	0.422	NULL	0
177894	41563.9239614815	20.842	NULL

Where 0.421 should be like 421.

Thanks

Re: Load text numbers

Anil_Babu_Samineni — Thu, 06 Oct 2016 05:27:07 GMT

What was the issue from here. Where ever DB stores samething here too which you get o/p from qlikview. is there chance to post the expected o/p

Re: Load text numbers

nagarjuna_kotha — Thu, 06 Oct 2016 06:15:05 GMT

Hey Harry,

try to post sample data , that covers ur issue.

that would be better for ur quick output.

-Nagarjun

Re: Load text numbers

Thu, 06 Oct 2016 08:54:03 GMT

Not sure, but i think you dont see my previous post, its being in await of a moderator.

Re: Load text numbers

sasiparupudi1 — Thu, 06 Oct 2016 09:14:33 GMT

Try

T1:

Load history_timestamp

,Num(purgechar(history_value,'.'),'#,##0.00') as history_value Inline

[

history_timestamp,history_value

2016-02-05 00:04:16,424.000

2016-02-05 00:09:40,0.426

2016-02-05 00:15:01,0.426

2016-02-05 00:20:37,427.000

2016-02-05 00:25:46,427.000

2016-02-05 00:31:07,428.000

2016-02-05 00:36:39,428.000

2016-02-05 00:42:02,428.000

2016-02-05 00:47:20,429.000

];

hth

Sasi

Re: Load text numbers

Thu, 06 Oct 2016 10:13:49 GMT

Thank you Sasidhar! this works!

Re: Load text numbers

Thu, 06 Oct 2016 13:13:53 GMT

Okay, it seemed working, but other results gets messed up now.

history_timestamp	field_description	Actual Air Flow OG0441	CO2Level OG2461
2016-02-05 08:29:40		97.942,000	-
2016-02-05 08:29:56		-	435,000
2016-02-05 08:34:46		11.158,000	-
2016-02-05 08:35:01		-	440,000
2016-02-05 08:39:55		96.895,000	-
2016-02-05 08:40:14		-	458,000
2016-02-05 08:45:04		122.063,000	-
2016-02-05 08:45:19		-	470,000
2016-02-05 08:50:13		268.797,000	-
2016-02-05 08:50:25		-	481,000
2016-02-05 08:55:19		250.725,000	-
2016-02-05 08:55:33		-	479,000
2016-02-05 09:00:30		271.441,000	-
2016-02-05 09:00:39		-	467,000
2016-02-05 09:05:40		301.372,000	-

All the Airflow data is now to much. The , should be on the . place. The 0.421 is solved, but the . character must not be removed since this is the decimal character. Any ideas?

The number properties i set to Fixed 3 decimals.

I think the problem lies also with that some numbers are still being loaded as text, since they are outlined on the left by default.