Load text numbers

[#1]

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

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

[swuehl]

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)?

[#3]

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

[Anil_Babu_Samineni]

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

[nagarjuna_kotha]

Hey Harry,

try to post sample data , that covers ur issue.

that would be better for ur quick output.

-Nagarjun

[#6]

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

[sasiparupudi1]

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

[#8]

Thank you Sasidhar! this works!

[#9]

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.