Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

josephinetedesc
Not applicable

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

1 Solution

Accepted Solutions
MarcoWedel
Not applicable

Re: How to group according to dimension - 5 timestamps

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

20 Replies
senpradip007
Not applicable

Re: How to group according to dimension - 5 timestamps

Could you please upload sample qvw?

josephinetedesc
Not applicable

Re: How to group according to dimension - 5 timestamps

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

Header 1Header 2Header 3Header 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 ....

  

        

PrimaryKeyIDProcedureTypeStaffNumProcedureCompleteFlagPersonprocedureDue_DtTmTimeCompletedidealTimeTakenInDays
1proceduretype5NULL1P1a12/11/2015 0:0013/11/2015 14:440
2proceduretype5NULL0P1b20/11/2015 0:00TBD8
3proceduretype6NULL1p10a2/11/2015 0:0017/11/2015 11:030
4proceduretype6NULL0p10b16/11/2015 0:00TBD14
5Proceduretype3NULL0p11a11/02/2016 0:00TBD0
6Proceduretype4NULL0p11b19/02/2016 0:00TBD8
7Proceduretype19050p12b15/12/2015 0:00TBD-1
8proceduretype5NULL1p13a9/11/2015 0:009/11/2015 17:170
9proceduretype59001p13b13/11/2015 0:0011/11/2015 15:069
10proceduretype8NULL0P2b13/11/2015 0:00TBD3
11proceduretype8NULL1P3a12/11/2015 0:0012/11/2015 12:450
12proceduretype89051p3b16/11/2015 0:0017/11/2015 9:454
13proceduretype6NULL1p4a20/11/2015 0:0020/11/2015 9:300
14proceduretype6NULL1p5a13/11/2015 0:0013/11/2015 12:160
15proceduretype8NULL1p6a13/11/2015 0:0013/11/2015 13:270
16proceduretype89001p6b17/11/2015 0:0017/11/2015 16:364
17Proceduretype29051p7b20/11/2015 0:0020/11/2015 13:143
18proceduretype5NULL1p8a13/11/2015 0:0013/11/2015 13:230
19proceduretype5NULL0p8b20/11/2015 0:00TBD7
20proceduretype618621p9a12/11/2015 0:0012/11/2015 14:280
21proceduretype69001p9b16/11/2015 0:0016/11/2015 14:02
josephinetedesc
Not applicable

Re: How to group according to dimension - 5 timestamps

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

balrajahlawat
Not applicable

Re: How to group according to dimension - 5 timestamps

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
Not applicable

Re: How to group according to dimension - 5 timestamps

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.

        

PersonActivityStampDate1StampDate2
p1a11/01/20152/01/2015
p1a22/01/20153/01/2015
p2a11/02/20152/02/2015
p2a23/01/20154/01/2015
PersonActivityActivityActivity1 StampDate1Activity1 StampDate2Activity2StampDate1Activity2 StampDate2sum(Activity2StampDate2 - Activity1StampDate2)
p1a1a2(a1)1/01/2015(a1)2/01/2015(a2)2/01/2015(a2)3/01/20153/01/2015 - 2/01/2015
p2a1a2(a1)1/02/2015(a1)2/02/2015(a2)3/01/2015(a2)4/01/20154/01/2014 -2/02/2015
balrajahlawat
Not applicable

Re: How to group according to dimension - 5 timestamps

Have you got the solution?? which  really matters

josephinetedesc
Not applicable

Re: How to group according to dimension - 5 timestamps

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. 

balrajahlawat
Not applicable

Re: How to group according to dimension - 5 timestamps

If you have data sth like you shared:

Tablle1:

PersonActivityStampDate1StampDate2
p1a11/01/20152/01/2015
p1a22/01/20153/01/2015
p2a11/02/20152/02/2015
p2a23/01/20154/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
Not applicable

Re: How to group according to dimension - 5 timestamps

no because ...

One person has 2 separate activities -

I need Activity2 StampDate2 - Activity1StampDate2