Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to create subsets in the script

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 TimePostCodeFirst Message for Postcode Timeframe GroupGroup ID
00:03:57a11
00:04:01a01
00:04:14a01
00:04:25a01
00:11:46a01
00:11:50a01
00:11:54a01
00:11:59a01
00:12:04a01
00:12:09a01
00:12:48a01
10:16:45a12
10:16:59a02
10:18:25a02
10:19:43a02
10:32:37a13
10:35:30a03
10:35:36a03
11:14:50a14
5 Replies

Re: How to create subsets in the script

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;


talk is cheap, supply exceeds demand
Not applicable

Re: How to create subsets in the script

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?

K

Re: How to create subsets in the script

Ok, let's try adding GroupID then:

Table1:

load MessageTime,

PostCode,

FirstMessage,

num(0) as GroupID

from ....


talk is cheap, supply exceeds demand
Not applicable

Re: How to create subsets in the script

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

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


talk is cheap, supply exceeds demand
Community Browser