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]

May be create a unique identifier in your script and add it as a dimension

LOAD ID,

           TECHNOLOGY,

           SEVERITY,

           RowNo() as UNIQUE_KEY

FROM ....;

and then add UNIQUE_KEY as a dimension. If this is a straight table, it can be hidden from the presentation tab

[jason_nicholas]

RowNo() generates 0 on every line. I've encountered that before, and I don't understand how it happens.

[sunny_talwar]

Do you have a preceding Load on top of this? May be try with RecNo() then

LOAD ID,

          TECHNOLOGY,

          SEVERITY,

          RecNo() as UNIQUE_KEY

FROM ....;

[sunny_talwar]

Check the attached image and qvw file

[Anonymous]

You should use SEVERITY as dimension!

(This is the way QlikView works.)

[jason_nicholas]

This doesn't generate the correct result. When Severity is the dimension, it skips the Technology data. Where Technology is the dimension, it skips Severity. In fact, there is a third field in this grouping (source) which I left out of my explanation.

[Anonymous]

???:

then set Severity and Technology as dimensions...

and don't supress zero values within the chart.

[Anonymous]

... what you call skipping is called only()

Have a look here: How to use - Only()

[jason_nicholas]

I may have simplified my explanation too much, because your solution works here but doesn't in my application. Let me back-track to see if I missed something.

My source data loads the term "Incident' in a field called "Resource". There are other data in the same field, so my first effort is to break out the Incident data. Whether the "Resource" is "Incident" or something else, the follow-on data is delivered in a field called "Selection". This is the reason for the temp fields and the WHERE statement in the Resident Load.

Event_Reference:
LOAD [Booking ID],
Resource as [RefResource Temp],
Selection as [RefSelection Temp],
FROM [..\DataSources\GMS Qlikview Event Reference.xls]

Then I follow up with this resident load, augmented by preceding loads. All of these steps pull the key data out of "Resource" and "Selection" and creates tables I can use

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';

This results in an internal table where each needed expression (Source, Technology, Severity) generates its own line. Where an incident has all three data points, there are three lines. If an item was skipped in the data entry, it has 2 lines. If, as in my original issue, someone adds in an additional Technology (for example) there is an additional line item added.

I follow this up by splitting Severity, Technology and Source into their own tables with resident loads:

Incident_Source:
LOAD [Booking ID],
[Source Temp] as [Incident Source],
[Incident Key]
Resident Incidents
Where [Source Temp]<> null();

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

Incident_Severity:
LOAD [Booking ID],
[Severity Temp] as [Incident Severity],
[Incident Key]
Resident Incidents
Where [Severity Temp]<> null();

Now I have three separate tables to reference, with no gaps in data. By loading the RecNo in my Incidents table and then recalling it to each detail table, my chart adds every data point, but on its own line. Without the RecNo dimension, I get the result shown above.