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.

[sunny_talwar]

Can you show a snapshot of what happens when you add RecNo()?

[jason_nicholas]

By adding the three dimensions (my example is only two, but there are three) as you suggested, and using the ID as the expression, I get:

[jason_nicholas]

[Anonymous]

I still think you haven't understood the difference between dimension and expression yet ...

Have a look at my sample:

[sunny_talwar]

May be try this:

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

;

Incidents:
LOAD [Booking ID],
[RefResource Temp] as Incident,  //This puts the term "Incident' in every cell, under the field header "Incident". Should be a 1,0 flag, but haven't cleaned it up yet
[RefSelection Temp] as [Incident Selection],
[Event Notes] as [Incident Notes],
RecNo() as [Incident Key]
RESIDENT Event_Reference WHERE [RefResource Temp] = 'Incident';

Incident_Source:
LOAD [Booking ID],
[Source Temp] as [Incident Source],
AutoNumber([Booking ID]) as [Incident Key]

Resident Incidents
Where [Source Temp]<> null();

Incident_Technology:
LOAD [Booking ID],
[Tech Temp] as [Affected Technology],
AutoNumber([Booking ID]) as [Incident Key]
Resident Incidents
Where [Tech Temp]<> null();

Incident_Severity:
LOAD [Booking ID],
[Severity Temp] as [Incident Severity],
AutoNumber([Booking ID]) as [Incident Key]

Resident Incidents
Where [Severity Temp]<> null();

[jason_nicholas]

This gives me a similar result to other methods.

Please note Booking ID 22627001. this has two incidents, with different severity and technology, but same source

For reference, Booking ID 23071679 is actually missing the incident source data, and the others are all complete and single entries

[Anonymous]

don't use any expression, have a look at my sample added above please.

[sunny_talwar · Accepted solution]

Give this a try now

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

;

Incidents:
LOAD [Booking ID],
[RefResource Temp] as Incident,  //This puts the term "Incident' in every cell, under the field header "Incident". Should be a 1,0 flag, but haven't cleaned it up yet
[RefSelection Temp] as [Incident Selection],
[Event Notes] as [Incident Notes],
RecNo() as [Incident Key]
RESIDENT Event_Reference WHERE [RefResource Temp] = 'Incident';

Incident_Source:
LOAD [Booking ID],
[Source Temp] as [Incident Source],
AutoNumber(RowNo(), [Booking ID]) as [Incident Key]

Resident Incidents
Where [Source Temp]<> null();

Incident_Technology:
LOAD [Booking ID],
[Tech Temp] as [Affected Technology],
AutoNumber(RowNo(), [Booking ID]) as [Incident Key]
Resident Incidents
Where [Tech Temp]<> null();

Incident_Severity:
LOAD [Booking ID],
[Severity Temp] as [Incident Severity],
AutoNumber(RowNo(), [Booking ID]) as [Incident Key]

Resident Incidents
Where [Severity Temp]<> null();

[jason_nicholas]

I am having limited success here, but I think there is a solution in your suggestion. I am going to set it aside for the night, and try again tomorrow. I will update with results/further questions/closed thread once I have completed.

thank you for the help.

[jason_nicholas]

This method only works if I don't add any other data into the chart. As soon as I start fleshing out the visualization, I end up with the null values once again.

The table needs to  show additional dimensions, such as [Month], [Building], [Details], and a hidden column for [Current Year Flag] to limit the results to 2017.