6 Replies Latest reply: Jun 15, 2016 5:42 PM by Tor Kielland RSS

    A chart expression pickle

    Tor Kielland

      Here's a real Qlik challenge!

       

      SourceTable:

      PeopleIDRoomIDTime
      1A03:02
      1A03:03
      1B03:04
      2B03:02
      2B03:03
      2B03: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_XRoomID_at_Time_YCount(Distinct PeopleID)
      AA0
      AB1
      BA0
      BB1

       

      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.

        • Re: A chart expression pickle
          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

          • Re: A chart expression pickle
            Tor Kielland

            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

              • Re: A chart expression pickle
                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

                  • Re: A chart expression pickle
                    Tor Kielland

                    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

                      • Re: A chart expression pickle
                        Stefan Wühl

                        What about something like this?

                          • Re: A chart expression pickle
                            Tor Kielland

                            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!