Solved: How to group according to dimension - 5 timestamps - Qlik Community

josephinetedesc · ‎2015-11-22

Every patient can have up to 5 timestamps.

I want to get the information (timestamp4 - timestamp3) Where should I do this? at the load level (does not work) - it loads a row of data for each datestamp, not per patient.

Is there a way of grouping all timestamps by patient - where should this be done at LOAD at SQL?

I can group by patient in a pivot table or a straight or pivot table. HOWEVER, I cannot do an average or total. I can use an if statement to only get the values which are greater than 0) However when I delete the patient dimension - the table will show no data at all.

Checked - yes this is possible but does not help

From Set Analysis literature I see I can use Sum(Sales) as a starting point : Is it possible to use Sum(SoldItems-ReturneItems) as a starting point?

Jo

Message was edited by: Josephine Tedesco

MarcoWedel · ‎2015-11-27

Hi,

one solution could be:

tabActivities:
LOAD Person,
    Activity,
    [dateStamp at completion],
    [dateStamp at start]
FROM [https://community.qlik.com/thread/192480] (html, codepage is 1252, embedded labels, table is @8);

Left Join (tabActivities)
LOAD Person,
    [dateStamp at start],
    If(Person=Previous(Person),[dateStamp at start]-Previous([dateStamp at start])) as LastActivityInterval
Resident tabActivities
Order By Person,[dateStamp at start];

hope this helps

regards

Marco

View solution in original post

senpradip007 · ‎2015-11-23

Could you please upload sample qvw?

josephinetedesc · ‎2015-11-23

In this sample I have 2 timestamps for some people - but it is the same "problem" - how do I get for example

Header 1

Header 2

Header 3

Header 4

Person

timestamp1

timestamp2

timestamp2-timestamp1

P1

Due_DtTm(for procedure a)

Due_DtTm(for procedure b)

(subtract one timestamp from the other)

Can I do this in Qlikview?

I have tried when loading to do a resident load - but I have not succeeded -however....I thought maybe I should see what I can achieve in Access to try and clear my head ....

PrimaryKeyID	ProcedureType	StaffNum	ProcedureCompleteFlag	Person	procedure	Due_DtTm	TimeCompleted	idealTimeTakenInDays
1	proceduretype5	NULL	1	P1	a	12/11/2015 0:00	13/11/2015 14:44	0
2	proceduretype5	NULL	0	P1	b	20/11/2015 0:00	TBD	8
3	proceduretype6	NULL	1	p10	a	2/11/2015 0:00	17/11/2015 11:03	0
4	proceduretype6	NULL	0	p10	b	16/11/2015 0:00	TBD	14
5	Proceduretype3	NULL	0	p11	a	11/02/2016 0:00	TBD	0
6	Proceduretype4	NULL	0	p11	b	19/02/2016 0:00	TBD	8
7	Proceduretype1	905	0	p12	b	15/12/2015 0:00	TBD	-1
8	proceduretype5	NULL	1	p13	a	9/11/2015 0:00	9/11/2015 17:17	0
9	proceduretype5	900	1	p13	b	13/11/2015 0:00	11/11/2015 15:06	9
10	proceduretype8	NULL	0	P2	b	13/11/2015 0:00	TBD	3
11	proceduretype8	NULL	1	P3	a	12/11/2015 0:00	12/11/2015 12:45	0
12	proceduretype8	905	1	p3	b	16/11/2015 0:00	17/11/2015 9:45	4
13	proceduretype6	NULL	1	p4	a	20/11/2015 0:00	20/11/2015 9:30	0
14	proceduretype6	NULL	1	p5	a	13/11/2015 0:00	13/11/2015 12:16	0
15	proceduretype8	NULL	1	p6	a	13/11/2015 0:00	13/11/2015 13:27	0
16	proceduretype8	900	1	p6	b	17/11/2015 0:00	17/11/2015 16:36	4
17	Proceduretype2	905	1	p7	b	20/11/2015 0:00	20/11/2015 13:14	3
18	proceduretype5	NULL	1	p8	a	13/11/2015 0:00	13/11/2015 13:23	0
19	proceduretype5	NULL	0	p8	b	20/11/2015 0:00	TBD	7
20	proceduretype6	1862	1	p9	a	12/11/2015 0:00	12/11/2015 14:28	0
21	proceduretype6	900	1	p9	b	16/11/2015 0:00	16/11/2015 14:02

josephinetedesc · ‎2015-11-23

I can group by Person in a pivot table or a straight or pivot table. HOWEVER, I cannot do an average or total.

When I delete the Person dimension - the table will show no data at all. For example to get an TOTAL of time taken by procedure type - I can delete the dimension - but the table then shows blank.

I think I will have to go back to the data model.

jo

Anonymous · ‎2015-11-23

Try sth like this:

Table1:

Load

PersonID,

'A' as Flag,

Due_DtTM as A_Due_DTTM

from Table where Procedure='a';

///It will do auto concatenate

Table2:

Load

PersonID,

'B' as Flag,

Due_DtTM as B_Due_DTTM

from Table where Procedure='b';

Now do resident load:

Final:

Load

*

A_Due_DTTM - B_Due_DTTM as Header4

resident Table1 group by all dimensions;

Hope this will help!!

josephinetedesc · ‎2015-11-26

I think I have found another way to explain the problem - it is difficult even in excel ...

I realise I need to do some fancy work here - but I am finding it very difficult. It is almost the reverse of the crosstable problem.

Person	Activity	StampDate1	StampDate2
p1	a1	1/01/2015	2/01/2015
p1	a2	2/01/2015	3/01/2015
p2	a1	1/02/2015	2/02/2015
p2	a2	3/01/2015	4/01/2015





Person	Activity	Activity	Activity1 StampDate1	Activity1 StampDate2	Activity2StampDate1	Activity2 StampDate2	sum(Activity2StampDate2 - Activity1StampDate2)
p1	a1	a2	(a1)1/01/2015	(a1)2/01/2015	(a2)2/01/2015	(a2)3/01/2015	3/01/2015 - 2/01/2015
p2	a1	a2	(a1)1/02/2015	(a1)2/02/2015	(a2)3/01/2015	(a2)4/01/2015	4/01/2014 -2/02/2015

Anonymous · ‎2015-11-27

Have you got the solution?? which really matters

josephinetedesc · ‎2015-11-27

no - I am going crazy i have tried and tried -

I have tried the following formula

if(Activity = 'a1', timestamp1, timestamp1) as time1,

if(Activity = 'a2', timestamp1, timestamp1) as time2,

but then i need to make the formula time2 - time 1 and I cannot substitute the if statements into the formula.

Anonymous · ‎2015-11-27

If you have data sth like you shared:

Tablle1:

Person	Activity	StampDate1	StampDate2
p1	a1	1/01/2015	2/01/2015
p1	a2	2/01/2015	3/01/2015
p2	a1	1/02/2015	2/02/2015
p2	a2	3/01/2015	4/01/2015

then I would say, make it very simple:

//use noconcatenate

Table2:

Load *,

StatmpDate2-StampDate1 as TotalDays

Resident Table1;

drop table Table1;

Hope this will help!!

One more thing, you want difference as a Days or Date??

josephinetedesc · ‎2015-11-27

no because ...

One person has 2 separate activities -

I need Activity2 StampDate2 - Activity1StampDate2