Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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".
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 | - | - |
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.
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.