Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Have an issue loading data and not sure how to resolve it.
My raw data set contains a set of values with NULL values in the data itself (sample attached)
When I load the data in using QV load script, it seems to duplicate values into the NULL cells on load when they are not there..
Data sample below..
Within QV the graph shows how QV has loaded it - the highlighted sections should be NULL values, but it has duplicated the items.
So it's SQL problem. I think you can try to change/reinstall the connction provider.
Or even try it on another mashine.
can you write the expressions you used in qv chart?
The problem is that when the data is loaded its populating the nulls with the previous values..
Even in a straight table I get the data showing ..
But in the Excel sheet the same query returns the correct null values ..
The load statement is as follows...
Business_Rules:
LOAD "Entity ID"as [Entity ID],
Entity,
if(Attribute = '','EBR','ABR') as [Business Rule Type],
Attribute,
Seqno as [EBR ID],
"Rule Name" as [Rule Name],
if([Rule Name]='Null Check','Completeness',if ([Rule Name]='Patterns Check','Validity',if ([Rule Name]='Predominant Datatype Check ','Validity',
if ([Rule Name]='Range Check','Validity',if ([Rule Name]='Schema Datatype Check','Validity',
if ([Rule Name]='Schema Length','Validity',if ([Rule Name]='Spaces Check','Usability',if ([Rule Name]='Sum Check','Accuracy',
if ([Rule Name]='Uniqueness Check','Uniqueness',if ([Rule Name]='Values Check','Consistency')))))))))) as [KDE Measurement],
Description,
Threshold,
Derived,
Library,
Enabled,
Result,
"Passing Fraction" as [Pasing Fraction],
"Failing Rows" as [Failing Rows],
"Passing Rows" as [Passing Rows],
Filtering,
Grouping,
"Group Count" as [Group Count],
"Group Limit" as [Group Limit],
Aggregating,
//IF(ISNULL("Passing Aggregate")=0, "Passing Aggregate",0 ) AS [Passing Aggregate],
"Passing Aggregate" as [Passing Aggregate],
"Failing Aggregate" as [Failing Aggregate],
//IF(ISNULL("Failing Aggregate")=0, "Failing Aggregate",0) AS [Failing Aggregate],
"Aggregate Method" as [Aggregate Method],
"Created By" as [Created By],
date("Date Created",'DD/MM/YYYY') as [Date Created] ,
"Edited By" as [Edited By],
"Date Changed" as [Date Changed],
Predicate as [Predicate],
"Filter Predicate" as [Filter Predicate],
"Group Function" as [Group Function],
"Aggregate Function" as [Aggregate Function],
Priority,
"Priority Name",
Categories;
//============================================================================================================================
//SQL select statement: change PID=1 to your selected Project ID from the Trillium Baseline Analysis ID
//============================================================================================================================
SQL SELECT "Entity ID",
Entity,
Attribute,
SeqNo,
"Rule Name",
Description,
Threshold,
Derived,
Library,
Enabled,
Result,
"Passing Fraction",
"Failing Rows",
"Passing Rows",
Filtering,
Grouping,
"Group Count",
"Group Limit",
Aggregating,
"Passing Aggregate",
"Failing Aggregate",
"Aggregate Method",
"Created By",
"Date Created",
"Edited By",
"Date Changed",
Predicate,
"Filter Predicate",
"Group Function",
"Aggregate Function",
Priority,
"Priority Name",
Categories
FROM show_all_br where pid=1;
Returns the right values in Excel ..
Could you post a screentshot of a table box instead of chart.
So in QV the TableBox shows
The highlighted item in Maximum Cover Validation is correct. The ones below actually contain no data .. but it seems to have copied them into the fields...
you can see ots also doing it for the Passing Aggregate as well as the Failing Aggregate
Ok. Really strange.
Could you check the Table tab in Document properties. There are two important columns # Values and # Distinct.
What am I looking for?
Your screen with table box.
I see all fields are from the same table. And it's not connected with others.
Maybe, SQL load problem. You can try to load from excel directly.
Also you can try to load only those 5 fields.
Weird.. if I load from Excel, I get the correct values
If I load from the Source via SQL .. it dupicates ..
try to use num or sum function
num( "Passing Aggregate") as [Passing Aggregate],
num( "Failing Aggregate") as [Failing Aggregate],