Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
skyline01
Creator
Creator

how to generate missing records in a join

I have table Tickets like:

Tickets:
Load * Inline [
TicketID, ChangeID
1, 10
2, 20
3,
4, 40
5,
6, 60
7, 60
8, 80
9, 90 ]
;

Not all tickets have an associated change.  Every ticket will have at most 1 associated change.  A given change can be associated with 1+ tickets.

I have table Change_Impacted_Locations, like:

Change_Impacted_Locations:
Load * Inline [
ChangeID, Location
10, a
40, b
40, c
60, d
60, e
90, a ] ;

Not every change will have a location impacted record.  A change can impact 1+ locations.  Naturally, a location can be associated with 1+ locations.

 

I need to append a dummy record to my Change_Impacted_Locations table for all distinct ChangeIDs (where populated) on the Tickets  table.  So, I need to generate something like:

Missing_Locations:
Load * Inline [
ChangeID, Location
20, dummy
80, dummy ] ;

And, I need these records concatenated into my Change_Impacted_Locations table.

 

How can I achieve this?

1 Solution

Accepted Solutions
skyline01
Creator
Creator
Author

I figured out the solution to my problem.

//need ChangeID name to be distinct
Tickets: Load * Inline [ TicketID, ChangeID on Tickets 1, 10 2, 20 3, 4, 40 5, 6, 60 7, 60 8, 80 9, 90 ] ;
//need ChangeID name to be distinct Change_Impacted_Locations: Load * Inline [ ChangeID on Impacted Locations, Location 10, a 40, b 40, c 60, d 60, e 90, a ] ; //will autoconcatenate to the Change_Impacted_Locations table Load "ChangeID on Tickets" as "ChangeID on Impacted Locations" ,'dummy' as Location Resident Tickets Where Not Exists("ChangeID on Impacted Locations", "ChangeID on Tickets") ;

View solution in original post

1 Reply
skyline01
Creator
Creator
Author

I figured out the solution to my problem.

//need ChangeID name to be distinct
Tickets: Load * Inline [ TicketID, ChangeID on Tickets 1, 10 2, 20 3, 4, 40 5, 6, 60 7, 60 8, 80 9, 90 ] ;
//need ChangeID name to be distinct Change_Impacted_Locations: Load * Inline [ ChangeID on Impacted Locations, Location 10, a 40, b 40, c 60, d 60, e 90, a ] ; //will autoconcatenate to the Change_Impacted_Locations table Load "ChangeID on Tickets" as "ChangeID on Impacted Locations" ,'dummy' as Location Resident Tickets Where Not Exists("ChangeID on Impacted Locations", "ChangeID on Tickets") ;