topic Re: Duplicate load on nulls problem in QlikView

Duplicate load on nulls problem

Thu, 30 Aug 2012 14:50:12 GMT

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.

Re: Duplicate load on nulls problem

Thu, 30 Aug 2012 18:03:16 GMT

can you write the expressions you used in qv chart?

Re: Duplicate load on nulls problem

Thu, 30 Aug 2012 18:43:45 GMT

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",

Re: Duplicate load on nulls problem

whiteline — Thu, 30 Aug 2012 18:54:28 GMT

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

Re: Duplicate load on nulls problem

Thu, 30 Aug 2012 18:58:54 GMT

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

Re: Duplicate load on nulls problem

whiteline — Thu, 30 Aug 2012 19:02:56 GMT

Ok. Really strange.

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

Re: Duplicate load on nulls problem

Thu, 30 Aug 2012 19:08:55 GMT

What am I looking for?

Re: Duplicate load on nulls problem

whiteline — Thu, 30 Aug 2012 19:11:30 GMT

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.

Re: Duplicate load on nulls problem

Thu, 30 Aug 2012 19:21:53 GMT

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

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

Re: Duplicate load on nulls problem

Thu, 30 Aug 2012 19:25:13 GMT

try to use num or sum function

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

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

Re: Duplicate load on nulls problem

Thu, 30 Aug 2012 19:31:54 GMT

No Joy ... tried a frsh app with clean SQL and still get the same issue ..

Re: Duplicate load on nulls problem

whiteline — Thu, 30 Aug 2012 19:40:45 GMT

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

Or even try it on another mashine.

Re: Duplicate load on nulls problem

Fri, 31 Aug 2012 15:51:07 GMT

Thanks White .. Seems to be an underlying issue with the connector generated SQL .. back to dev with it

Re: Duplicate load on nulls problem

Mon, 03 Sep 2012 11:17:32 GMT

Well, tested the connector in SQL directly and it is working fine.. seems to be something QV is doing ..

any ideas anyone?

Re: Duplicate load on nulls problem

whiteline — Mon, 03 Sep 2012 11:22:32 GMT

You can try it with another QV version (11, 10, 9).

Re: Duplicate load on nulls problem

Mon, 03 Sep 2012 11:28:43 GMT

Using V11 ..

dont have access to another one unfortunatley

Re: Duplicate load on nulls problem

Mon, 03 Sep 2012 11:36:40 GMT

I tried

IF(ISNULL("Passing Aggregate")=-1, "Passing Aggregate",0 ) AS [Passing Aggregate],

to spot the nulls .. but it seems to populate every field with 0 as well ..