topic find next value using script in QlikView

find next value using script

Mon, 15 Aug 2011 14:52:23 GMT

Hi everyone,

I have a table showing peoples names and the date that thy were attended to. I am using the PREV statement in the script to look up the date difference between a previous incident and that incident.

What has been pointed out though is that users want to see if the incident resulted in a follow up attendance.

so instead of looking for all incidents which was the follow up, I need to know which incidents were the precursor to a follow up.

Is this possible? And how would I do this?

find next value using script

swuehl — Mon, 15 Aug 2011 15:05:07 GMT

Hi,

you may want to have a look into the chapter 23.6 ( semantic tables) in the reference manual.

I think you could use a semantic table to hold information for preceding / following visits.

You might also want to look into the presidents demo qvw.

Hope this helps,

Stefan

find next value using script

matt_crowther — Mon, 15 Aug 2011 15:09:59 GMT

Phil,

If I understand your situation correctly it sounds as though you're most of the way there with the previous() function.

I'm assuming that amoungst your data table you have fields that are something similar to an 'IncidentID' and 'AttendedTime', if so the following should get you started:

Lets say the table you have is simply called 'Data' and have already loaded it in your script.

Qualify *;

Data_2:

Load *,

if(previous(IncidentID)=IncidentID,'Precursor','First/Final') as Indicator;

NoConcatenate Load

*; // Your data fields

Resident Data Order By IncidentID Asc, AttendedTime Desc;

*I haven't checked that syntax but the principal is there (I use it in the wild).

Essentially what you're doing is grouping the incidents together then ordering them with the most recent AttendedTime at the top of each group - ie the last time they were seen; this can be derived by looking at the previous IncidentID; if it's different we must be looking at a final visit (of the one and only) of a seperate incident. Likewise if the row above has the same IncidentID then we must be looking at a precursor.

You can also do the following to show how many visits there have been and the gap between first and last:

Unqualify *;

Left Join (Data_2)

Load IncidentID,

count(IncidentID) as Visits,

max(AttendedTime)-min(AttendedTime) as TotalTime;

Resident Data_2 Group By IncidentID;

As mentioned the syntax isn't checked so you may need to make some slight changes.

Any problems; post an example file.

Hope that helps,

Matt - Visual Analytics Ltd