multiple entries in source data cause null results

[jason_nicholas]

My application has a function to display and report on incidents, and I am having an issue where there are multiple incidents reported in a single booking. The chart shows null results where there are two or more options to choose from

LOAD * INLINE[

ID, TECHNOLOGY, SEVERITY

12345, Video, Minimal  

55555, Audio, Minimal       //incident one, microphone battery died

55555, Audio, Moderate    //incident two, audio system crashed

];

If I build a chart with ID as the dimension and TECHNOLOGY  and SEVERITY as the expressions, the result I get looks like this:

ID	TECHNOLOGY	SEVERITY
12345	Video	Minimal
55555	Audio	-
55555	Audio	-

I have to note that my simplified example here will not actually display the two 55555 entries. I solved that problem through a more complicated process which would make this question a little muddy. (In the load, I used an IF statement to determine if my data falls in this category and create a flag. The actual first dimension is the flag, and it is hidden and has null results ignored. The result looks like above)

Because TECHNOLOGY is the same for both incidents in ID 55555, the table has no problem returning the value "Audio". But when I ask for the SEVERITY associated with ID 55555, the result is null because it doesn't know whether to return "Minimal" or "Moderate". Every single-incident entry displays properly, and the issue only comes in when there are more than one value to choose from in the association.

The result I am looking for would look like this:

ID	TECHNOLOGY	SEVERITY
12345	Video	Minimal
55555	Audio	Minimal
55555	Audio	Moderate

I want the ability to display the first incident of an ID on the first line and the second on a second line with all related data. Which should appear first and which should appear second would be based on load order from the original source data.

I'd appreciate any help you could offer.

[jason_nicholas]

I think I am still missing something in this method. [Incident Key] is loaded as RecNo() in the Incidents table, and as AutoNumber(RowNo()) in the breakout loads. I tried this, and I tried renaming the RecNo() version, but both give me the same results.

I get a synthetic table which relates Booking ID to the Incident Key. This part seems to work well, in that any time there are two entries for a booking, they are given sequential Incident Key numbers. But with an example like Booking ID 22627001, which has two incidents and all three data points associated with each (Severity, Source, and Technology), it generates 5 distinct [Incident Key], and gives me 5 lines when I need two. Most data points end up on their own line, but [Incident Key] 3 in that set adds two data points on one line. I don't know what is different here.

All of the single incident bookings are placed on two or three separate lines (depending on how many data points are entered)

[sunny_talwar]

My bad, don't load [Incident Key] is loaded as RecNo() in the Incidents table, just load it as AutoNumber(RowNo()) in the breakout loads.

[jason_nicholas]

I thought that was the case. That is why I tried to rename it. The image posted above is WITHOUT RecNo() Incident Key in the Incidents table

[sunny_talwar]

use the one from breakout loads

[jason_nicholas]

Yes, the Incident Key in the image above is generated by

AutoNumber(RowNo(), [Booking ID]) as [Incident Key]

in each breakout table. It creates a synthetic key and splits the items up over different lines.

[sunny_talwar]

Would you be able to provide some information on what you are getting (image with the new AutoNumber(RowNo(), [Booking ID]) as [Incident Key]) and an Excel file or some other way to detail what you expect to see... Ideally a sample or sample of raw data might be very helpful

[jason_nicholas]

My table is working now. I eliminated [Incident Key] altogether and removed the Resident loads. I use the source Event Reference table with the IF statement Preceding Loads. That, combined with some strict source data entry rules, solves my problem.

I'm left with the issue of separating the other, non-incident data from the Event Reference table, but that's the next issue to tackle. I'm marking the RowNo() solution as correct, as I learned a lot about how that function affects the data, but in my case, the solution was to back out of the effort and take another path.

I appreciate the help.

  LOAD *,
if(MATCH([Selection],'Minimal','Moderate','Severe'), [Selection]) as [Severity] ;
LOAD *,
if(MATCH([Selection],'Audio', 'Video', 'Presentation'),[Selection]) as [Tech] ;
LOAD *,
if(MATCH([Selection],'Technician Error','Technology Error','User Error'),[Selection]) as [Source] ;

Event_Reference:
LOAD [Booking ID],
Resource.
Selection,

if(MATCH(Resource,'Incident'),'Incident')as Incident

FROM [..\DataSources\GMS Qlikview Event Reference.xls]