Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michael089
Contributor
Contributor

Qlik Sense Selection Issue/Challenge

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?

5 Replies
chris3669
Contributor III
Contributor III

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.

michael089
Contributor
Contributor
Author

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".

chris3669
Contributor III
Contributor III

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--
michael089
Contributor
Contributor
Author

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.

chris3669
Contributor III
Contributor III

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