Script Voodoo: Data conversion conundrum

Thu, 14 Oct 2010 12:33:31 GMT

Hey Folks,

I have one of those irritating 'how might I do the following' questions, as opposed to one of those 'I'm doing this, and it doesn't work' questions.

I have a somewhat complex problem, and I think I'm overcomplicating. I'm hoping you can give me some suggestions?

The metric that I'm after is member segment mobility. Essentially, I want to know how my people change as time changes.

Let's imagine I have a table called 'Members', on that table there is a field called 'Segment'. Segment is an input from another system that I don't have transparency into, and I don't know how it's calculated, it's just something I get. A member's segment can change from day to day, and it could stay the same forever.

My system keeps historical copies of all records, so I have a history of what the segment has been historically, and could tell you when it changed for any given member. (So, I could in theory create a table that had the fields: Member ID, Segment, Change Date) The system keeps a copy of the record each time it changes.

The requirement I'm attempting to implement is that I want to know (with at least monthly granularity) how a given portion of my population breaks down into segment at some selected point in the past.

For example, for this discreet group of members that I can somehow identify (let's just say, everyone who made a purchase in 2008), what was their segment in January of 2009, and how has that set group of customers changed between then, and now?

The visualization is the easy part. The hard part is transforming data I have, into what I need.

I've tried a couple of approaches without much success. I can accomplish what I need for a single member, but the solution doesn't translate into multiple members.

I hope that's at least reasonably clear?

Effectively, I need to turn the table below into a table which contains an entry for the start of every month, and the value of the member's 'Segment' field on that date.

Here's a sample of what my source data could look like:

LOAD * INLINE [

Member ID, Segment, Change Date

1, BLUE, 1/12/2010

1, RED, 2/24/2010

1, BLUE, 7/14/2010

1, ORANGE, 10/10/2010

2, BLUE, 1/1/2010

2, PURPLE, 1/2/2010

2, RED, 10/11/2010

2, PURPLE, 10/25/2010

3, PURPLE, 1/1/2010

];

Script Voodoo: Data conversion conundrum

pover — Thu, 14 Oct 2010 21:07:19 GMT

How about if you try using the an inter-row function like previous to create a table like the following:

LOAD * INLINE [

Member ID, Segment, Change Date, Until Date

1, BLUE, 1/12/2010,2/23/2010

1, RED, 2/24/2010,7/13/2010

1, BLUE, 7/14/2010,10/9/2010

1, ORANGE, 10/10/2010,12/31/9999

2, BLUE, 1/1/2010,1/1/2010

2, PURPLE, 1/2/2010,10/10/2010

2, RED, 10/11/2010,10/24/2010

2, PURPLE, 10/25/2010,12/31/9999

3, PURPLE, 1/1/2010,12/31/9999

];

And then use intervalmatch with a master calendar to link this table with the master calendar to know the segment of the customer at any given date.

How's that sound?

Script Voodoo: Data conversion conundrum

Thu, 14 Oct 2010 23:09:40 GMT

Karl:

Awesome! This solution seems much better than my kludge. I'm traying to understand exactly what is going on with the interval match, and what happens at the unbounded ends (ie: the beginning of time, and the end of time) but I think this is doing exactly what I need!

Thanks!

topic Script Voodoo: Data conversion conundrum in QlikView

Script Voodoo: Data conversion conundrum

Script Voodoo: Data conversion conundrum

Script Voodoo: Data conversion conundrum