date problems and arithmetic values

[josephinetedesc]

Hi my problem is that I have date 0 in Crystal reports coming in as 01/01/1800

Qlikview is recognising the date:

so in Qlikview the dates look like this:

my formula is:

if(time4-time3 <0, 0, time4-time3)

if I do not use an if statment I get this: the same result

I end up with problems when tryting to add the column Time4-time3 in qlikview but not in Excel

Patient	location	time4-time3	Timestamp(time4)	timestamp(time3)
1	AREA a	0.0	11/11/2015 4:45:02 PM	11/11/2015 4:44:04 PM
2	AREA a	1.9	12/11/2015 9:27:54 AM	10/11/2015 11:24:14 AM
3	AREA a	0.0	####################	######################
4	AREA a	5.7	12/11/2015 9:58:09 AM	6/11/2015 4:03:56 PM
5	AREA a	0.0	####################	11/11/2015 2:42:55 PM
6	AREA a	-	-	-
7	AREA a	0.0	####################	11/11/2015 9:39:07 AM
8	AREA a	4.1	13/11/2015 12:33:32 PM	9/11/2015 10:32:21 AM
9	AREA a	0.0	####################	13/11/2015 1:27:37 PM
10	AREA a	0.0	####################	13/11/2015 3:32:57 PM
11	AREA a	0.0	####################	######################
12	AREA a	-	-	-
13	AREA a	0.0	####################	12/11/2015 2:28:54 PM
14	AREA a	0.0	10/11/2015 3:40:20 PM	10/11/2015 3:21:51 PM
15	AREA a	0.0	####################	######################
16	AREA a	0.0	####################	######################
17	AREA a	-	-	-
18	AREA a	0.0	####################	######################
19	AREA a	0.0	####################	9/11/2015 2:16:07 PM
20	AREA a	1.9	11/11/2015 3:06:41 PM	9/11/2015 5:17:50 PM
21	AREA a	0.0	####################	11/11/2015 9:04:14 AM
22	AREA a	0.0	####################	######################
23	AREA a	-	-	-
average	0.7
sum	13.7
count	19
average	0.719567495

My other problem is that when I remove the Patient dimension - I get no data?  I do not understand why.  I am sure there is a reason ...

What do I need to do in Qlikview to get the aritmetic values?

Jo

[swuehl]

What is your data source you are using in QV? I think it would be best to already correct the data when reading in.

Could you post some sample data records? And the expected result?

[josephinetedesc]

would like something like this:  those rows with all hash not to be counted ..  They are not counted because either timestamp 3 or timestamp 4 has a value of -35617 - ie nothing has been done yet.

Patient	location	time4-time3	Timestamp(time4)	timestamp(time3)
1	AREA a	0.0	11/11/2015 4:45:02 PM	11/11/2015 4:44:04 PM
2	AREA a	1.9	12/11/2015 9:27:54 AM	10/11/2015 11:24:14 AM
3	AREA a	0.0	##################	######################
4	AREA a	5.7	12/11/2015 9:58:09 AM	6/11/2015 4:03:56 PM
5	AREA a	0.0	##################	11/11/2015 2:42:55 PM
6	AREA a	-	-	-
7	AREA a	0.0	##################	11/11/2015 9:39:07 AM
8	AREA a	4.1	13/11/2015 12:33:32 PM	9/11/2015 10:32:21 AM
9	AREA a	0.0	##################	13/11/2015 1:27:37 PM
10	AREA a	0.0	##################	13/11/2015 3:32:57 PM
11	AREA a	0.0	##################	######################
12	AREA a	-	-	-
13	AREA a	0.0	##################	12/11/2015 2:28:54 PM
14	AREA a	0.0	10/11/2015 3:40:20 PM	10/11/2015 3:21:51 PM
15	AREA a	0.0	##################	######################
16	AREA a	0.0	##################	######################
17	AREA a	-	-	-
18	AREA a	0.0	##################	######################
19	AREA a	0.0	##################	9/11/2015 2:16:07 PM
20	AREA a	1.9	11/11/2015 3:06:41 PM	9/11/2015 5:17:50 PM
21	AREA a	0.0	##################	11/11/2015 9:04:14 AM
22	AREA a	0.0	##################	######################
23	AREA a	-	-	-
	average	0.7
	sum	13.7
	count	6
	average	2.278630401

[swuehl]

Ok, but these are not your input records, right? How do these look like?

Maybe it's enough to do something like

LOAD

     Patient,

     location,

     if(time3 >0, time3, NULL() ) as time3,

     if(time4 >0, time4, NULL() ) as time4,

     ...

FROM ...;

This should only take time stamps after Dec 30 1899 into acount.

[josephinetedesc]

so would i do a resident load for this?  I would still like to see ALL patients.

I will see what I can do at work tomorrow.

Thank you will let you know