topic Re: Values incorrectly displaying as NULL in a Straight Table Chart in QlikView

Values incorrectly displaying as NULL in a Straight Table Chart

jason_nicholas — Wed, 25 Nov 2020 16:16:04 GMT

I am having trouble sorting some data onto a straight table chart. Certain values aren’t being displayed in the appropriate field, although the value exists in the source data. To make the matter more confusing, this is only happening with certain data points, while others are displaying properly. I can find no difference between the two data sets which would cause this.

I hope I am able to clearly explain my issue, as our security policy prevents uploading any content to share.

I’ll start at the beginning, because my problem could be caused by an error in the way I load the data. The source material has two relevant columns, “Resource” and “Selection”. The possible values in “Selection” are based on the value in “Resource”. My function deals with only one specific resource (“Incident”), so my first step is to separate the data associated with that resource from the rest.

Within the Incident resource, the values are grouped into three categories (Severity, Technology, Source), each having three possible values.

Severity- Minimal, Moderate, Severe Technology- A, B, C Source- 1, 2, 3

To make it more complicated, the category groupings aren’t listed at all, so I have to work backwards to create those groups. My source data might look like:

ID	Resource	Selection
12345	Incident	Minimal
12345	Incident	2
12345	Incident	B
12345	(unrelated resource)	(unrelated data)
11111	Incident	Moderate
11111	Incident	A

The first thing I do is separate the Incident data from the rest. In my primary LOAD statement, I have:

IF(MATCH([Resource], ‘Incident’),’Incident2’) as [Incident3],

This creates a tag to separate Incident data from the unrelated resources, which I can use in functions later.

Next, I use preceding loads to separate the Selection data into the three relevant categories.

LOAD *, IF(MATCH([Selection], ‘Minimal’, ‘Moderate’, ‘Severe’), [Selection] as [Severity];

This is repeated for the Technology and Source groupings.

Next, I want to create a table which shows only entries with a related incident, and display the Severity, Technology, and Source of each. I use a straight table chart showing the ID number of the entry as the dimension, and three expressions to display the needed values:

IF([Incident3]=’Incident2’,[Severity]) (if the data references an incident, display the Severity value)

IF([Incident3]=’Incident2’,[Source]) (if the data references an incident, display the Source value)

IF([Incident3]=’Incident2’,[Technology]) (if the data references an incident, display the Technology value)

This seems to work for the most part. The issue I am having is that some values in my expression columns are not displaying, while others are. I can find no difference between the ones displaying correctly and the ones missing values.

The resulting table might look like this:

ID	Severity	Source	Technology
12345	Minimal	2	A
99999	Moderate	-	B
55555	-	1	-
00000	Severe	2	-
11111	-	-	A

Each entry should have a complete data set, as 12345 does. If I add a field to the page where I can select Source, I can choose 3 and it will bring up 99999 and the Source value will be 3 instead of NULL. If I don’t make a selection, the same entry displays NULL instead.

My question is, what could be preventing existing values from displaying on the straight table, when they exist in the source data? Why is QV reading a result as NULL , when it associates the correct value with the ID when a selection is made?

Re: Values incorrectly displaying as NULL in a Straight Table Chart

marcus_sommer — Thu, 09 Feb 2017 12:35:29 GMT

Of course often it's useful to pre-calculate or flag things within the script. But I'm not sure that your approach will lead you in the right direction - for this you should take one step after another by adding recno() and rowno() to your loads and check the results after each load - just adding a exit script; after the load and moving then this statement to next load after you succeed with it - within a tablebox if the data are like you expected them.

Beside them I think I would just load the source-data like they are and then using set analysis expressions like this:

count({< Resource = {'Incident'}, Selection = {'Minimal'} >} ID)

count({< Resource = {'Incident'}, Selection = {'Moderate'} >} ID)

count({< Resource = {'Incident'}, Selection = {'Severe'} >} ID)

- Marcus

Re: Values incorrectly displaying as NULL in a Straight Table Chart

jason_nicholas — Thu, 09 Feb 2017 14:34:11 GMT

In my case, I am not looking for a count of the instances of each type (well, I am, but that is a set analysis in another chart, which works much like you have listed here). Instead, I am trying to produce a text table which outlines the incidents that happened under a set of circumstances.

With no selections, the list should show all of the incidents in the current data. If I choose a month, or a severity, or location, etc. (all of which are available in my data), the table should filter accordingly. This is the reason I took the approach to move the needed data out of the generic "selection" column in my source data and put it in a dedicated field in the QV table.

A single ID entry could have 5-10 records, each with different information in the "resource" and "selection" columns, but I need the ability to show a table of this data separated into the relevant columns- in this case, Severity, Source, and Technology. In fact, moving data from the generic columns provided by the source data into dedicated fields is the number one priority of the entire dashboard- and the rest is just added value.

What really has me stumped is why it works in some cases and not in others. ID 12345 above is sourced the same way as the others, and it displays properly. ID 99999 failed to return the "Source" data which is associated with that ID in this table, but if I were to select '3' (expected value) from a selection field, the association would come up- likely missing one or both of the other data points.

Regarding your first note, I have been trying to understand the purpose of rowno() and recordno(). I understand what they return, but I am not sure what it does to improve a dashboard. I am going to load these items and try it myself, but I would appreciate it if you would expand on this suggestion a little bit and help me understand what I am looking for as a result.

Re: Values incorrectly displaying as NULL in a Straight Table Chart

tunoi — Thu, 09 Feb 2017 14:44:24 GMT

I believe that that ID 99999 has more that one Source and because of that qlikview don't know which one to display. you need some sort of aggregation function like min(IF([Incident3]=’Incident2’,[Source]) ) applied on your expression.

Re: Values incorrectly displaying as NULL in a Straight Table Chart

marcus_sommer — Thu, 09 Feb 2017 14:59:03 GMT

I must admit that I do not quite understand your approach and would rather do the following to get a table like your output-table:

LOAD ID, Source, Technology, Selection as Severity

From Source

Where MATCH([Selection], ‘Minimal’, ‘Moderate’, ‘Severe’) and Resource = 'Incident';

The suggestion of putting recno() and rowno() within the tables and displaying them within tableboxes are only to be able to validate the (source and transformed) data from the tables within the gui. You need to be absolutely sure on each step that your data are what you think they should be.

- Marcus

Re: Values incorrectly displaying as NULL in a Straight Table Chart

jason_nicholas — Thu, 09 Feb 2017 15:11:43 GMT

QV won't recognize LOAD Source, Technology, Selection

From Source

There are no existing fields which separate these. I need to create them in my function. The possible values of each (Minimal, Moderate, Severe, 1, 2, 3, A, B, C) are all just considered "Selection" in the source data. There are a number of other, unrelated, data points also listed in "Selection". They are all grouped based on the field "Resource".

The problem I am encountering is with the incident data, because the Selection options are grouped into three additional categories (Severity, Source, Technology), but all 9 results in my example are viewed by the source system as all part of the same group. It is only the text content that differentiates between them.

For your example above, if [Selection] matched Minimal, Moderate, Severe, then [Resource] will ALWAYS be 'Incident'. But that is also true if [Selection] is 1,2,3 or A, B, C. I just know the selections that need to be identified as Technology, Severity, or Source based on the limited number of options available for each.

Re: Values incorrectly displaying as NULL in a Straight Table Chart

Peter_Cammaert — Thu, 09 Feb 2017 15:12:14 GMT

Ionut Bostan has a good point.

To debug /analyse this "multiple possible values in a single cell"-situation, add a table box with all the base fields you use in your Straight table, and see what value combinations pop up when you do/don't make selections.

BTW Min() only works on numerical data. MinString() or MaxString() might be a better tool here. They both ignore NULL values if there is anything better available.

Re: Values incorrectly displaying as NULL in a Straight Table Chart

jason_nicholas — Thu, 09 Feb 2017 15:14:57 GMT

when I add MIN( to the function, the table clears of all data.

I was hoping it would load the lowest, non-NULL result associated with a particular ID, which would work because there would only be one non-NULL result to display in that condition. But instead, it is unable to find any results that fit that condition, applied to all three expressions.

Re: Values incorrectly displaying as NULL in a Straight Table Chart

jason_nicholas — Thu, 09 Feb 2017 15:37:31 GMT

If I correctly segregate the source data into Source, Technology and Severity, there should not be multiple values. Either there was an incident related to an ID or not. If there was, there is one Source, one Severity and one Technology to list.

Actually, it is possible to have more than one value for each of these, if more than one incident happened associated with a particular ID. That isn't the case in my source data at this time, so isn't an indicator of the issue I am having. It is a problem I have to solve at some point

When I add MinString( to each expression, I get a table with all of the data, but each ID has multiple line items; one for each piece of data. This is exactly what my source data looks like, except for the added benefit of moving Severity, Source, and Technology data into their own fields instead of the Selection field. If I could now force this to put all related data on a single line item, I would have what I need.

Here's the resulting table:

RecNo()	RowNo()	Booking ID	Severity	Source	Technology
78	0	20168867	Moderate	-	-
79	0	20168867	-	-	Presentation
80	0	20168867	-	User Error	-
407	0	21970069	-	-	Audio Conference
408	0	21970069	Minimal	-	-
409	0	21970069	-	User Error	-

This should be two entries, and no blanks. I did selectively choose the entries with a complete data set. There are some where the users failed to enter one data point or another, and blanks will be expected in that case.

Also, I am showing here the results of adding RecNo() and RowNo(). I don't understand why RowNo() doesn't return any values, but I wonder if that is a clue to my mistake?

Re: Values incorrectly displaying as NULL in a Straight Table Chart

tunoi — Thu, 09 Feb 2017 15:42:04 GMT

that's is happening because you have column recno and rowno, if you eliminate those you should have only one row per Booking ID

i used min in my example because your field [Source] was numeric in the table above

Re: Values incorrectly displaying as NULL in a Straight Table Chart

jason_nicholas — Thu, 09 Feb 2017 15:49:01 GMT

That did it!

And I see where that would cause a problem. I substituted 123 and ABC to simplify the discussion, but never indicated that it was all text. Thanks for the help.