Qlik Community

App Development

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

Announcements
QlikWorld online is next week! REGISTER NOW
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") ;