Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
could you post some sample data??
Person | Activity | dateStamp at completion | dateStamp at start | ||||
John | Run segment | 1/01/2015 | 2/01/2015 | ||||
John | Swim segment | 2/01/2015 | 3/01/2015 | ||||
Paul | Run segment | 1/02/2015 | 2/02/2015 | ||||
Paul | Swim segment | 3/01/2015 | 4/01/2015 |
so time difference between the completion of the Run Segment and the Swim segment for each person ...
so John took 1 day to complete both activities
Paul took 2 days to complete both activities
Will this work?
PFA..
Cannot test until work on Monday ... but I know will bug me all weekend! Will let you know! My next thing will be to try and see if I can lease a licence ... I read about doing that somewhere!
Hi Jo,
Check out this qvw, it gives you a pivot table like this using your data:
procedure | a | b | ||
---|---|---|---|---|
Person | Due_DtTm | Diff | Due_DtTm | Diff |
P1 | 12/11/2015 | 8 | 20/11/2015 | |
P2 | 13/11/2015 | |||
P3 | 12/11/2015 | |||
p3 | 16/11/2015 | |||
p4 | 20/11/2015 | |||
p5 | 13/11/2015 | |||
p6 | 13/11/2015 | 4 | 17/11/2015 | |
p7 | 20/11/2015 | |||
p8 | 13/11/2015 | 7 | 20/11/2015 | |
p9 | 12/11/2015 | 4 | 16/11/2015 | |
p10 | 02/11/2015 | 14 | 16/11/2015 | |
p11 | 11/02/2016 | 8 | 19/02/2016 | |
p12 | 15/12/2015 | |||
p13 | 09/11/2015 | 4 | 13/11/2015 |
As you add more procedures for these patients to the data the table will expand out to the right.
Cheers
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
Hi Marcus dose this code rely on the original data being in Person order? Is does Previous(Person) a function??? I will look it up! I was going to ask how did you get 29 - but ... I think the date format is MM/DD/YYYY?
Jo
Hi,
Previous(Person) reads the value of the Person field in the previously loaded record.
The load has to be sorted, yes, that's why the resident load has "order by Person" defined.
Please close this thread if your question is answered.
thanks
regards
Marco
Thank you Marco and Balraj.
It was good learning about the Previous/Peek function.
Jo