Re: Qlik Sense Selection Issue/Challenge - Qlik Community

michael089 · ‎2018-01-11

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?

chris3669 · ‎2018-01-11

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 · ‎2018-01-12

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 · ‎2018-01-12

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?

row	id_ticket	date_ticket	id_subticket	date_subticket
1	T1	Jan	ST1	Jan
2	T1	Jan	ST2	Feb
3	T2	Dec	ST3	Jan
4	T3	Nov	-	-

michael089 · ‎2018-01-18

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 · ‎2018-01-19

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 [