16 Replies Latest reply: Sep 3, 2012 7:36 AM by robin Miller RSS

    Duplicate load on nulls problem

    robin Miller

      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

        • Re: Duplicate load on nulls problem
          Wojciech Parzyszek

          can you write the expressions you used in qv chart?

            • Re: Duplicate load on nulls problem
              robin Miller

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