Sign InHelp

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can learn more about Qlik Sense App Development and Usage.

- Qlik Community
- :
- Qlik Sense
- :
- Qlik Sense Enterprise
- :
- Qlik Sense Enterprise Discussions
- :
- Qlik Sense App Development
- :
- A chart expression pickle

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

06-14-2016
05:36 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Solved! Go to Solution.

266 Views

1 Solution

Accepted Solutions

swuehl

MVP

06-15-2016
04:22 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

What about something like this?

6 Replies

marcus_sommer

MVP

06-15-2016
01:44 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Not applicable

06-15-2016
09:08 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

MVP

06-15-2016
11:03 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Not applicable

06-15-2016
02:41 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

MVP

06-15-2016
04:22 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

What about something like this?

Not applicable

06-15-2016
05:42 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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!