Convert start/stop to list of days

Fri, 15 Sep 2017 16:18:18 GMT

Hi everyone,

I've spent some time searching online for a solution to this but most of the examples I run into use dates/times to create ranges and it doesn't seem to fit our use case.

I have data stored in a database in the format below describing when a user performs an "action".

I need to re-format this data to show which users performed an action each day. The total number of actions per user do not matter, I just need to know active/inactive (0/1) for each user.

Source Data:

User Id	Action Start Day	Action End Day
'A'	1	1
'B'	3	4
'C'	5	5
'A'	1	3

SourceData:

LOAD * Inline [

User, StartDay, EndDay

'A', 1, 1

'B', 3, 4

'C', 5, 5

'A', 1, 3

];

The data represents "users" and the days they performed an action.

For example:

User 'A' started an action on day 1, then finished on day 1.

User 'B' started an action on day 3, then finished on day 4.

User 'C' started an action on day 5, then finished on day 5.

User 'A' started another action on day 1, then finished that action on day 3. (actions can span days and overlap with other actions)

User 'A' performed 2 actions on day 1, but we only care if any action was performed that day or not. We don't need the total number of actions. The data could have "overlaps" in multiple rows, but we don't care about the total.

I would like to convert this to the following format showing "buckets" representing days each user performed an action: (if you have a better suggestion to format the data, I am open to ideas)

Desired output data to visualize in QlikSense:

User	Day 1	Day 2	Day 3	Day 4	Day 5
'A'	1	1	1	0	0
'B'	0	0	1	1	0
'C'	0	0	0	0	1
Total	1	1	2	1	1

The goal of the output table is to create a "Total" of all active users per day, so we can visualize that total for each day by totaling each Day. Note that User 'A' was performed 2 actions on day 1 in the source table, but each user is only counted once in the output table.

Note: The example above is simplified. There are 100+ days in our output table, so hopefully there's a way to do this without assigning each day in the load script.

For example:

On day 1: 1 user was active

On day 2: 1 user was active

On day 3, 2 users were active

etc.....

I'm not married to the data formats above, although the first table represents an over-simplified version of how our data is currently stored in the database. We can change that format if needed.

Any ideas/suggestions on how to translate the first table into the 2nd table? Thanks in advance!

Re: Convert start/stop to list of days

Anonymous — Fri, 15 Sep 2017 17:00:08 GMT

This could well be a candidate for the IntervalMatch() function as described in this Blog IntervalMatch

All the concepts are identical QlikView vs Sense.

Re: Convert start/stop to list of days

kaushiknsolanki — Fri, 15 Sep 2017 17:06:29 GMT

Try this script.

SourceData:

LOAD * Inline [

User, StartDay, EndDay

'A', 1, 1

'B', 3, 4

'C', 5, 5

'A', 1, 3

];

Temp:

Load RowNo() as Day

AutoGenerate 10;

Interval:

IntervalMatch (Day)

Load StartDay, EndDay Resident SourceData;

After reloading this script.

Create a pivot table with Day and User as dimension and Count(Distinct User) as expression.

Regards,

Kaushik Solanki

topic Re: Convert start/stop to list of days in App Development

Convert start/stop to list of days

Re: Convert start/stop to list of days

Re: Convert start/stop to list of days