Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
josephinetedesc
Creator III
Creator III

How to group according to dimension - 5 timestamps

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

20 Replies
Anonymous
Not applicable

could you post some sample data??

josephinetedesc
Creator III
Creator III
Author

PersonActivitydateStamp at completiondateStamp at start
JohnRun segment1/01/20152/01/2015
JohnSwim segment2/01/20153/01/2015
PaulRun segment1/02/20152/02/2015
PaulSwim segment3/01/20154/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

Anonymous
Not applicable

Will this work?

PFA..

josephinetedesc
Creator III
Creator III
Author

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!

Anonymous
Not applicable

sure.

this will help you to lease a license as well:

QlikView License Leasing

effinty2112
Master
Master

Hi Jo,

Check out this qvw, it gives you a pivot table like this using your data:

procedure ab
Person Due_DtTmDiffDue_DtTmDiff
P112/11/2015820/11/2015 
P2 13/11/2015 
P312/11/2015  
p3 16/11/2015 
p420/11/2015  
p513/11/2015  
p613/11/2015417/11/2015 
p7 20/11/2015 
p813/11/2015720/11/2015 
p912/11/2015416/11/2015 
p1002/11/20151416/11/2015 
p1111/02/2016819/02/2016 
p12 15/12/2015 
p1309/11/2015413/11/2015

As you add more procedures for these patients to the data the table will expand out to the right.

Cheers

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_192480_Pic1.JPG

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

josephinetedesc
Creator III
Creator III
Author

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

MarcoWedel

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

josephinetedesc
Creator III
Creator III
Author

Thank you Marco and Balraj.

It was good learning about the Previous/Peek function.

Jo