A chart expression pickle

[#1]

Here's a real Qlik challenge!

SourceTable:

PeopleID	RoomID	Time
1	A	03:02
1	A	03:03
1	B	03:04
2	B	03:02
2	B	03:03
2	B	03:04

OutputTable:

This would be two dimensions and one measure in a straight table. Variable X is 03:02. Variable Y is 03:04.

RoomID_at_Time_X	RoomID_at_Time_Y	Count(Distinct PeopleID)
A	A	0
A	B	1
B	A	0
B	B	1

What's the logic I'm aiming for:

• I want to monitor the flow of people between rooms
• At each Time, each PeopleID is in one RoomID
• For two points in time (X and Y), I want to know how many distinct people that followed each of the possible flows between the rooms.
• (The combinations A,A and B,A don't exist in the SouceTable, so they would not really be included in the OutputTable. I just included them to illustrate each possible combination of rooms.)

Using SQL, I would go from SourceTable to OutputTable like this:

SELECT s1.roomID as Room_at_Time_X, s2.roomID as Room_at_Time_Y, count(distinct s1.peopleID) as CountDistinctPeopleID

FROM SourceTable s1

cross join SourceTable s2

where s1.time='03:02' and s2.time='03:04'

group by s1.roomID, s2.roomID

Why would I want to achieve this output data structure?

Because I want to use one of the D3 sankey charts that has been posted on branch. They are fantastic, but they require this lame structure: One dimension for each of the points in time and only one measure.

A big thanks to whoever cracks this one!

Regards,

Tor

NB - I posed a similar question a month back, but without luck. I'm trying to pose it more concisely here.

[marcus_sommer]

I'm not sure if you need such a table-structure. I think I would transform your time into a from-field and to-field with Peek() or Previous() ? and then match these field to a master timetable per IntervalMatch‌ and using later relative simple expressions like count(PeopleID) within the objects. It might not answer each question but very probably more then you could get with such a table.

- Marcus

[#3]

Hey Marcus

Thanks for the suggestion. And normally, from and to fields linked to a master timetable would get me far.

...but in this case, my challenge is to arrive at the structure of the "OutputTable". I don't see any possible workarounds as there is no alternative data structures accepted by the extensions in question.

Thanks!

Tor

[marcus_sommer]

One way would simply be to keep your sql-statement within qlikview-script (are you sure that you get with these sql and the above displayed sample-data those output?) and make all further transformings/calculations on these table (what is the reason that the ouput-table must have these structure?).

- Marcus

[#5]

Hi Marcus

Yes, you are probably right. Unless someone is able to make this data transformation with a Qlik expression, I'll have to prep it in the database.

The disadvantage of this becomes clear if you look at my data: I've got millions of points in time (in addition to tens of rooms and thousands of people). The OutputTable is difficult to prep, because the results will be different based on which points in time that the user wants to look at.

Marcus: what is the reason that the ouput-table must have these structure?

This structure is required by the D3 sankey diagrams (e.g. this one) that has been adapted for Sense.

Thanks again (I'll mark the answer correct unless I get some other good suggestions in the next day or two)

Tor

[swuehl · Accepted solution]

What about something like this?

[#7]

That's pretty impressive, Stefan. Ingeniously simple!

Just to explain, in case somebody else reads this: The suggestion is to duplicate the source table in memory. (You can make as many tables as you want, each table representing one point in time.) Naming e.g. like this:

TableX: PeopleID, RoomID_at_Time_X, Time_X

TableY: PeopleID, RoomID_at_Time_Y, Time_Y

Instead of using variables to set Time_X and Time_Y in some kind of smart function, each of these can be filtered with a single time value.

OutputTable: Dimensions: RoomID_at_Time_X, RoomID_at_Time_Y; Measures: Count(Distinct PeopleID)

Thanks!