Daltons@ the therapy: How long did they stay, did they meet and was one following another?

[andreas_koehler]

Dear all,

The story is: There are three guys - Joe, Jack and Averell - who went into therapy at about the same time to become honest citizens. They got in, stayed for a while and got out again to come back some days later. Each one has its own cycle.

I have the dates each one goes into therapy and the dates each one is released again as 'healed'.

I need to figure out :

- Duration of each therapy (Days between 'In' and 'Out'),

- Number of days when more than one band member is in therapy  and who-meet-whom during therapy,

- and finally what is the number of days after one band member went into therapy again that another one joined him.

Any help to get started is highly appreciated.

I attached a file with some data and I hope that you enjoy this example.

Andreas

[sunny_talwar]

I was able to work on the first two requirements listed above, not sure what you needed or how you would want to visualize the last one.

Script changes (in Orange)

Diary:

LOAD * INLINE [

    %Date, Patent, Status

    04.04.15, Joe, In

    06.04.15, Joe, Out

    07.04.15, Joe, In

    08.04.15, Joe, Out

    16.04.15, Joe, In

    19.04.15, Joe, Out

...

];

NewDiary1:

LOAD %Date,

  Patent,

  Status,

  If(Patent = Peek('Patent'), If(Peek('Status') = 'In', Peek('Flag'), RangeSum(Peek('Flag'), 1)), 1) as Flag

Resident Diary

Order By Patent, %Date;

NewDiary2:

LOAD Flag,

  %Date as In_Date,

  Patent

Resident NewDiary1

Where Status = 'In';

Left Join (NewDiary2)

LOAD Flag,

  %Date as Out_Date,

  Patent

Resident NewDiary1

Where Status = 'Out';

DROP Tables Diary, NewDiary1;

[TempMinMaxTime]:

Load

Min(In_Date) as minTimeStamp,

Max(Out_Date) as maxTimeStamp

resident NewDiary2;

LET vMinDate = Floor(Peek('minTimeStamp',0,'TempMinMaxTime'));

LET vMaxDate = Ceil(Peek('maxTimeStamp',0,'TempMinMaxTime'));

...

IntervalMatch:

IntervalMatch(%Date)

LOAD In_Date,

  Out_Date

Resident NewDiary2;

Lets take a peek at the new data model

Don't be scared to see the synthetic key here. Not a bad thing in this case. Read HIC's comment here: IntervalMatch‌

First Requirement

Second Requirement

To determine total number of days, you can use this in a text box object -> =Sum(Aggr(If(Count(DISTINCT Patent) > 1, 1), %Date))

Finally, attaching the qvw for you to play around with. Also, elaborate a little more on the third requirement so that one of us can help you better

[andreas_koehler]

Sunny,

thank you very much for your answer. Sorry for the delayed response but today is a public holiday in Germany.

I reckoned that this would be a great sandbox to learn more about the art of scripting and hoped to get some kind of starting point only to be blown away again by both the capabilities of Qlikview and the generosity of this great community.

Before I dig into your answers I will add to the third requirement:

While I compared the status of the same patient at different times with the frist requirement, I now compare the status of different patients.

I would select two patients via inputfields (e.g. Joe vs Averell).

The hypothetical question would be if Joe triggered Averell to become dishonest again.The criterion is that in more than 50% of the cases Averell entered the therapy in <= 3 days after Joe got dishonest again and was sent to therapy.

Best Regards,

Andreas

[swuehl · Accepted solution]

What do you expect to see for Joe followed by Averell within 3 days? 51,4%?

Attached a possible solution (which can probably be simplified / optimized a lot, I was just playing around with some ideas and now I have to stop here).

I am also not exactely sure how you want to count the 'cases', since there can be multiple IN's for a given Patent in short time frame.

Also you might want to link the Impact table to the other tables, but I am not sure what the appropriate key should be.

Well, just have a look and play around.

[For the first part with the DateLink table and NewDiary, I followed roughly

Creating Reference Dates for Intervals

]

[sunny_talwar]

This look good Stefan. I was going to work on this sometime tonight... But I guess I won't have to anymore .

But to reiterate what you mentioned. Without knowing what the expected output, I was having difficult imagining what would be an expected output.

Best,

Sunny

[andreas_koehler]

Sunny,

it took me some time with the help of the Qlikview manual and the linked blog to understand this beautiful solution.

I learnt about the skillful use of 'order by', peek and intervall match and most importantly how to understand the problem:

Your solution to requirement 1:

In NewDiary1 you created a flag to count the visits of each patient and ensured that even with mixed data entries they are ordered properly (dates ascending as visits always start with 'IN').

With NewDiary2 you create an intervall table based on visits and dates, dropping the 'Status'-strings as they are of no use in the calculations. This allowed the straight forward calculation of the duration per visit.

Your solution to requirement 2:

I read about intervall match at some time but it hasn't reached my processing unit 😉

This one links the intervall table NewDiary2 with the %Date in the master calendar filling the gaps.

Very nice solution.

I am curious how this performs when I scale this up.

[sunny_talwar]

I don't think the performance will go down once the application is scaled, but that is something you can test. Stefan provided another way (avoiding Interval Match table) but I won't be able to comment which one would be more efficient. I guess the best way to know would be to test it out

[andreas_koehler]

Stefan,

thanks a lot for providing this solution. I am happy that I looked at Sunny's solution for 1 and 2 first as this was a good preparation to understand your solution which is more condensed. Following the link you provided I also found HIC's Generating Missing Data In QlikView which has been immensly valuable and which is also a great read.

Walking through your solution till I get stuck:

- As the data source is already sorted you directly prefix rangesum- counter to generate the analogon of Sunny's Flag for visits as TherapyID using Status = 'IN' of each actual row as condition and peek TherabyID value of the previous row. What differs to Sunny's solution is that this TherapyID is irrespective of the patient. There is only 1 TherapyID = 1 while there is FLAG=1 for each patient in Sunny's solution.

- In the TMP table the resident inline table including the newly created TherapyID field is loaded first and a new TEMP field is created that calculated the difference between each dates in the resident table (with the absolute numerical value for the first date  as previous (%Date) = NULL). This field contains both the days 'at' the therapy ('OUT'- 'IN') and the days 'out' of the therapy ('IN'-'OUT').

- This data is piped to the frist load of the TMP table, a load-iterno-while loop using the days 'at the therapy' as counter. This is done by the IF-clause as while condition: WHILE iterno() <= If(Status = 'In',1, Temp ).

Now I am stuck:

1. why the substraction in Date(%Date - (iterno()-1)) as %Date ? Shouldn't it be Date(%Date + (iterno()-1)) as %Date ?
2. What is the purpose of previous(%Date) as Temp ?
3. Is the frist load within TMP automatically receiving all fields from the second load as there is no load statement for 'Status' and 'Temp' ?

Any help is - as always - very much appreciated.

Andreas

[swuehl]

Andreas,

in my example, there is also a counter for therapy per patent, TherapyPatentID field in DateLink table.

1) I think both suggested solutions would benefit if your input table would show the data like

   Patent, InDate, OutDate

    Joe,  04.04.15, 06.04.15

Then my first two table load statements could be simplified to:

TMP:

LOAD Patent, Date(InDate+iterno()-1) as %Date, Recno() as TherapyID

FROM NewTableStructure

WHILE InDate+Iterno()-1 <= OutDate;

[The current TMP table creation is a little over complicated, it just creates a %Date value for each date of the therapy. use an Exit Script; statement after the Drop Field Temp; statement and have a look at the resulting table.]

2) No purpose. I used this for debugging and forgot to remove it (the field is dropped anyway).

3) It's retrieving all fields in the input table (from the LOAD statement below the preceding LOAD), but only outputs a subset of fields to the output table.