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

Duplicate load on nulls problem

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

data.jpg

Within QV the graph shows how QV has loaded it - the highlighted sections should be NULL values, but it has duplicated the items.

graph.jpg

1 Solution

Accepted Solutions
whiteline
Master II
Master II

So it's SQL problem. I think you can try to change/reinstall the connction provider.

Or even try it on another mashine.

View solution in original post

16 Replies
Not applicable
Author

can you write the expressions you used in qv chart?

Not applicable
Author

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

whiteline
Master II
Master II

Could you post a screentshot of a table box instead of chart.

Not applicable
Author

So in QV the TableBox shows

pt.jpg

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

whiteline
Master II
Master II

Ok. Really strange.

Could you check the Table tab in Document properties. There are two important columns # Values and # Distinct.

Not applicable
Author

What am I looking for?

abc.jpg

whiteline
Master II
Master II

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.

Not applicable
Author

Weird.. if I load from Excel, I get the correct values

abc.jpg

If I load from the Source via SQL .. it dupicates ..

Not applicable
Author

try to use num or sum function 

num( "Passing Aggregate") as [Passing Aggregate],

  num(  "Failing Aggregate") as [Failing Aggregate],