3 Replies Latest reply: Jan 12, 2018 11:53 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--