Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jason_nicholas
Creator II
Creator II

multiple entries in source data cause null results

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:

IDTECHNOLOGYSEVERITY
12345VideoMinimal
55555Audio-
55555Audio-

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:

IDTECHNOLOGYSEVERITY
12345

Video

Minimal
55555AudioMinimal
55555AudioModerate

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.

1 Solution

Accepted Solutions
sunny_talwar

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();

View solution in original post

26 Replies
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
Creator II
Creator II
Author

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

Capture.PNG

Anonymous
Not applicable

You should use SEVERITY as dimension!

(This is the way QlikView works.)

jason_nicholas
Creator II
Creator II
Author

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
Not applicable

???:

then set Severity and Technology as dimensions...

and don't supress zero values within the chart.

Anonymous
Not applicable

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

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

jason_nicholas
Creator II
Creator II
Author

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.