topic Re: Group if in Qlik Automate

Group if

alexdelagarza — Tue, 24 Sep 2024 23:29:20 GMT

Hi,

I have the following table:

Id_viaje	Id_User	From	To	Time
1	U123	A	B	11:14:00
3	U123	B	C	11:38:12
5	U123	C	D	11:53:52
2	U123	A	B	17:36:05
4	U123	B	C	17:59:03
6	U123	C	D	18:13:25

As you can see, there are travels made in the morning, and others made in the afternoon, What I need is to group those travels made one after another with a difference in time no longer than 30 minutes to consider them as one long travel from A to D. At the end there would be to long travels one made in the morning and other made in the adternoon, both from A to D.

Is important to note that if the individual travel that was done with a difference loger than 30 minutes can not be considered as part of a group of travels but as an individual one.

Id_viaje	Id_User	From	To	Time
1	U123	A	B	11:14:00	Individual
3	U123	B	C	13:00:00	Individual
5	U123	C	D	15:00:00	Individual
2	U123	A	B	17:36:05	Group
4	U123	B	C	17:59:03
6	U123	C	D	18:13:25

Thanks in advance

Re: Group if

marcus_sommer — Wed, 25 Sep 2024 12:20:55 GMT

It could be done with interrecord-functions within an appropriate sorted resident-load, here a simplified example as starting point:

load *, if(TimeDiff <= 1/24/60*30, 'Group', 'individual') as Flag;
load User, Time, if(User = previous(User), Time - previous(Time), null()) as TimeDiff
resident X order by User, Time;

Re: Group if

alexdelagarza — Wed, 25 Sep 2024 22:55:09 GMT

Hi Marcus,

I see in this expression that you're using previous(), but, what if I want to evaluate not the previous record but the following. Since my first travel is From: A To: B, my start point is "A", and I need to know if the next travel which is From: B To: C is part of the same travel, and obviously the evaluation will continue to the following travels such as the third one wich is From: C To: D.

Best

Re: Group if

marcus_sommer — Thu, 26 Sep 2024 06:13:08 GMT

With sort-suffixes of asc (it's the default) and desc you could adjust the sort-order, like:

... resident X order by User asc, Time desc;

Depending on the real complexity of your requirement you may also add the From/To fields to the sort-order and/or combining some fields in beforehand (to simplify the sorting respectively sometimes are sorting-challenges only solvable with it).

Further possible and occasionally needed is to run such sorting multiple times forward and backwards with more (nested) if-loops to flag a multi-level ordering and on top of it the final grouping/accumulation is applied. If so are several simple steps after another easier to develop and to maintain as doing everything within a single load.

Re: Group if

Kushal_Chawda — Thu, 26 Sep 2024 09:24:56 GMT

@alexdelagarza In below example what will be the output.

d_viaje	Id_User	From	To	Time
1	U123	A	B	11:14:00	Individual
3	U123	B	C	13:00:00	Individual
5	U123	C	D	13:25:00	Group?

Re: Group if

alexdelagarza — Fri, 27 Sep 2024 23:01:47 GMT

Hi Marcus,

Following your indications, I did this:

Timediff:
load *, if(TimeDiff <= 1/24/60*30, 'Group', 'individual') as Flag;
load Id_User,
Time,
if(Id_User = previous(Id_User), previous(Time) - Time, null()) as TimeDiff,
If(Id_User = previous(Id_User), previous(Id_viaje), null()) as Next_travel
resident Datos order by Id_User, Time desc;

And this is the output:

Id_viaje	Id_User	From	To	Time	Flag	TimeDiff	Next_travel	Comments
1	U123	A	B	11:14:00	Group	00:24:12	3	Ok
2	U123	A	B	17:36:05	Group	00:22:58	4	Ok
3	U123	B	C	11:38:12	Group	00:15:40	5	Ok
4	U123	B	C	17:59:03	Group	00:14:22	6	Ok
5	U123	C	D	11:53:52	individual	05:42:13	2	TimeDiff and Next_travel comes from Id_viaje = 2 but they are not related, answer should be null.
6	U123	C	D	18:13:25	individual			Ok

Please check my comments, also Flag column is not working well I think, but probably it won't be necessary at least by now.

Best

Re: Group if

alexdelagarza — Fri, 27 Sep 2024 23:06:09 GMT

Hi Kushal,

I just posted the table.

Thanks

Re: Group if

Kushal_Chawda — Sat, 28 Sep 2024 18:20:56 GMT

@alexdelagarza If from A->B and B->C time diff is 25 Mins but C->D 1 hour the for all the travel we need to Flag Group or two Group & One Individual?

Re: Group if

marcus_sommer — Mon, 30 Sep 2024 05:55:15 GMT

Like hinted above you may need some more measurements and/or considering a higher complexity. The main-logic behind the use of interrecord-functions like previous/peek is quite simple:

query a current key against the previous one
accumulate a previous value with the current value respectively calculate any offset between them
(optional) deriving any flags or categories from the results

In many scenarios it's not really complicated even if there exists a suitable key and/or the sorting-field else if it needs to be created by n steps in beforehand in which multiple fields are (conditionally) combined to serve the purpose.

In your case it seems that you need to order the locations and the users before you compares the time-offsets. I don't know how complex this might be and if it's sensible to implement it within a single logic. Like above mentioned are n simple steps often easier as one complex one which means it might be more practically to include n parallel check-routines and the final categorizing is performant against all results.

More simple expressed: one logic may compares the offsets and another checks for any exceptions with a simple TRUE/FALSE flag and everything with FALSE is FALSE/NULL or whatever is suitable.