5 Replies Latest reply: Jan 19, 2018 6:49 AM by Christian Nogueira RSS

    Qlik Sense Selection Issue/Challenge

    Michael Pascher

      Hello,

       

      I hope someone can help me with the following question:

       

      I have two related tables, the first one contains tickets, the second one subtickets. In both tables I have a  column "Month".

      Here is a simplified version of these tables with columns ticket ID, subticket ID and month.

       

      Table A:

       

      T1 | Jan

      T2 | Dec

      T3 | Nov

       

      Table B:

       

      ST1 | T1 | Jan

      ST2 | T1 | Feb

      ST3 | T2 | Jan

       

      Tables are related via Ticket ID.

       

      When I now select "Jan", QlikSense displays T1 from table A and ST1 and ST2 from table B.  But I want to see T1, ST1, and ST3 as these are the tickets from "Jan".

      I can of course use SET to break the selection for the visualization of table B, but I don't want to do a manual selection for both tables. Is there some way to automatically select the month in Table B once I do a selection for Table A?

        • Re: Qlik Sense Selection Issue/Challenge
          Christian Nogueira

          If the Month of Table A and Month of Table B have the same field name, it is going to be part of the join of the tables

          To prevent it (as the Month of T2 is Dec in Table A and Feb in Table B) Load the second table with another name in the field holding the month.

           

          TableA:

          Load * inline [

          id_ticket, date_ticket

          T1, Jan

          T2, Dec

          T3, Nov];

           

          TableB

          Load * inline [

          id_subticket, id_ticket, date_subticket

          ST1, T1, Jan

          ST2, T1, Feb

          ST3, T2, Jan];

           

          Now selecting the date_subticket will yield what you want (ST1 and ST3), as the join will not use date as the key.

          Qlik is all about the name of the fields

           

          If you need a mix of eg.: the minimum date of the join, the first step is to convert it to a date variable, and later you can use if(date_ticket < date_subticket, date_ticket, date_subticket) in your view.

            • Re: Qlik Sense Selection Issue/Challenge
              Michael Pascher

              Hi Christian,

               

              thanks for your response & thoughts.

               

              I simplified my tables a bit and I should have been more precise about this. Actually, the month fields in both tables have different names - the relation is via ticket_id. Unfortunately, selecting the month for the field date_subticket is not a solution. I think selecting "Jan" from date_subticket will give me ST1 and ST3 from TableB and T1 and T2 from TableA. And ticket T2 was issued in Dec :-(

               

              To get the correct result, I would first have to select "Jan" from date_ticket and then (on the next sheet maybe) delete the first selection and select "Jan" from date_subticket so see all the (sub)tickets that were issued in "Jan".

            • Re: Qlik Sense Selection Issue/Challenge
              Christian Nogueira

              Can you provide a sample of the target output you want? It ll make it easy to help you

              Here is the join from both tables, can you tag which rows do you want?

                

              rowid_ticketdate_ticketid_subticketdate_subticket
              1T1JanST1Jan
              2T1JanST2Feb
              3T2DecST3Jan
              4T3Nov--
                • Re: Qlik Sense Selection Issue/Challenge
                  Michael Pascher

                  Hi Christian,

                   

                  sorry for the late response.

                   

                  So for instance, I want to see the number of tickets and subtickets that were issued in January (so T1, ST1, and ST3).

                  Expected result:

                  - Tickets: 1

                  - Subtickets: 2

                   

                  If I use a filter pane with the column "date_ticket" and select "Jan" it gets me rows 1 and 2.Note, in row 2 ST2 was issued in "Feb".

                  Result: Tickets: 2; Subtickets: 2

                   

                  If I use a filter pane with the column "date_subticket" and select "Jan" it gets me rows 1 and 3. Note: in row 3 T2 was issued in "Dec".

                  Result: Tickets: 2; Subtickets: 2

                   

                  So the issue is I have two timelines - one for tickets and one for subtickets. I cannot solve this problem using Qlik's standard selection mechanism. I would have to use SET expressions in the visualization (e.g. in separate bar charts for tickets and subtickets), but then I'm loosing the flexibility of selecting different timeframes. I was just wandering if there was a smarter design such situations.

                    • Re: Qlik Sense Selection Issue/Challenge
                      Christian Nogueira

                      Hi,

                       

                      You can change a bit the structure to achieve the desired view

                      In each load, can create a new field that will generate a new join in the information

                       

                      TableA:

                      Load

                          *,

                          id_ticket as ticket,

                          date_ticket as date_item

                      inline [

                      id_ticket, date_ticket

                      T1, Jan

                      T2, Dec

                      T3, Nov];

                       

                      TableB:

                      Load

                          *,

                          id_subticket as ticket,

                          date_subticket as date_item

                      inline [

                      id_subticket, id_ticket, date_subticket

                      ST1, T1, Jan

                      ST2, T1, Feb

                      ST3, T2, Jan];

                       

                      With this, when you select the "date_item" it will return T1, ST1, and ST3.

                      2018-01-19 09_48_28-teste_christian - Minha nova pasta _ Pastas - Qlik Sense.png