topic multiple entries in source data cause null results in QlikView

multiple entries in source data cause null results

jason_nicholas — Mon, 27 Mar 2017 15:10:56 GMT

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.

Re: multiple entries in source data cause null results

sunny_talwar — Mon, 27 Mar 2017 15:17:12 GMT

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

Re: multiple entries in source data cause null results

jason_nicholas — Mon, 27 Mar 2017 15:50:07 GMT

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

Re: multiple entries in source data cause null results

sunny_talwar — Mon, 27 Mar 2017 15:51:16 GMT

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

Re: multiple entries in source data cause null results

sunny_talwar — Mon, 27 Mar 2017 15:53:57 GMT

Check the attached image and qvw file

Re: multiple entries in source data cause null results

Anonymous — Mon, 27 Mar 2017 16:11:47 GMT

You should use SEVERITY as dimension!

(This is the way QlikView works.)

Re: multiple entries in source data cause null results

jason_nicholas — Mon, 27 Mar 2017 17:31:40 GMT

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.

Re: multiple entries in source data cause null results

Anonymous — Mon, 27 Mar 2017 17:38:40 GMT

???:

then set Severity and Technology as dimensions...

and don't supress zero values within the chart.

Re: multiple entries in source data cause null results

Anonymous — Mon, 27 Mar 2017 17:42:33 GMT

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

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

Re: multiple entries in source data cause null results

jason_nicholas — Mon, 27 Mar 2017 18:32:07 GMT

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.

Re: multiple entries in source data cause null results

sunny_talwar — Mon, 27 Mar 2017 18:39:03 GMT

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

Re: multiple entries in source data cause null results

jason_nicholas — Mon, 27 Mar 2017 18:41:21 GMT

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:

Re: multiple entries in source data cause null results

jason_nicholas — Mon, 27 Mar 2017 18:42:35 GMT

Re: multiple entries in source data cause null results

Anonymous — Mon, 27 Mar 2017 18:47:35 GMT

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

Have a look at my sample:

Re: multiple entries in source data cause null results

sunny_talwar — Mon, 27 Mar 2017 18:50:47 GMT

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

Re: multiple entries in source data cause null results

jason_nicholas — Mon, 27 Mar 2017 19:16:42 GMT

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

Re: multiple entries in source data cause null results

Anonymous — Mon, 27 Mar 2017 19:18:18 GMT

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

Re: multiple entries in source data cause null results

sunny_talwar — Mon, 27 Mar 2017 19:20:19 GMT

Give this a try now

;

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

Re: multiple entries in source data cause null results

jason_nicholas — Mon, 27 Mar 2017 19:32:17 GMT

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.

Re: multiple entries in source data cause null results

jason_nicholas — Tue, 28 Mar 2017 12:10:03 GMT

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.