Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
What about something like this?
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
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
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
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
What about something like this?
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!