Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
Honored Contributor II

Re: Duplicate load on nulls problem

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

Or even try it on another mashine.

16 Replies
Not applicable

Re: Duplicate load on nulls problem

can you write the expressions you used in qv chart?

Not applicable

Re: Duplicate load on nulls problem

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
Honored Contributor II

Re: Duplicate load on nulls problem

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

Not applicable

Re: Duplicate load on nulls problem

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
Honored Contributor II

Re: Duplicate load on nulls problem

Ok. Really strange.

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

Not applicable

Re: Duplicate load on nulls problem

What am I looking for?

abc.jpg

whiteline
Honored Contributor II

Re: Duplicate load on nulls problem

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

Re: Duplicate load on nulls problem

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

Re: Duplicate load on nulls problem

try to use num or sum function 

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

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

Community Browser