topic Re: How to create subsets in the script in QlikView

How to create subsets in the script

Wed, 03 Oct 2012 17:35:02 GMT

I am really stumped and would welcome some help. I have QVDs created from daily log files from our appointment booking software that shows me all the requests for appointments and the dates offered for each request. Our customer advisers can submit multiple requests per customer for different date ranges to best meet the customer's needs. I want to identify the earliest date offered per customer amongst the various requests. The only customer reference I have is postcode and we get requests from the same postcode that are hours apart and need to be handled separately. I would like to group the messages for the same postcode that have occurred within 10 minutes of each other and need a way to flag which messages are part of the same set and which are from a later call. I can flag the first message for the grouping I want but am struggling to assign each group a unique Group ID that I could then use to group the data by in my script. I have attached the table below

Message Time	PostCode	First Message for Postcode Timeframe Group	Group ID
00:03:57	a	1	1
00:04:01	a	0	1
00:04:14	a	0	1
00:04:25	a	0	1
00:11:46	a	0	1
00:11:50	a	0	1
00:11:54	a	0	1
00:11:59	a	0	1
00:12:04	a	0	1
00:12:09	a	0	1
00:12:48	a	0	1
10:16:45	a	1	2
10:16:59	a	0	2
10:18:25	a	0	2
10:19:43	a	0	2
10:32:37	a	1	3
10:35:30	a	0	3
10:35:36	a	0	3
11:14:50	a	1	4

Re: How to create subsets in the script

Gysbert_Wassenaar — Wed, 03 Oct 2012 17:59:36 GMT

Maybe something like this:

Table1:

load MessageTime,

PostCode,

FirstMessage

from ....

Table2:

load MessageTime,

PostCode,

FirstMessage

if(FirstMessage =1,peek(GroupID)+1,peek(GroupID)) as GroupID;

resident Table1;

drop table Table1;

Re: How to create subsets in the script

Wed, 03 Oct 2012 18:28:06 GMT

Hi,

Thank you very much for responding so quickly. I have tried your suggestion but seem to get nulls for GroupID - is that becuase it doesn't exist in the original data? I tried something similar with previous and RowNo() but only managed to create the same ID for the first 2 members of each group. Am I missing something obvious?

Re: How to create subsets in the script

Gysbert_Wassenaar — Wed, 03 Oct 2012 18:41:26 GMT

Ok, let's try adding GroupID then:

Table1:

load MessageTime,

PostCode,

FirstMessage,

num(0) as GroupID

from ....

Re: How to create subsets in the script

Wed, 03 Oct 2012 18:51:58 GMT

Sorry - still not working for me. I have attached the test QVW that I have built. Maybe you can spot something I have done wrong?

Re: How to create subsets in the script

Gysbert_Wassenaar — Wed, 03 Oct 2012 19:39:55 GMT

Ok, something that does work. I had to do some studying to figure this out properly