- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok, let's try adding GroupID then:
Table1:
load MessageTime,
PostCode,
FirstMessage,
num(0) as GroupID
from ....
talk is cheap, supply exceeds demand
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok, something that does work. I had to do some studying to figure this out properly
talk is cheap, supply exceeds demand