13 Replies Latest reply: Jan 12, 2017 11:07 AM by Charles Esquibel RSS

    Count of Rows in Table does not match Table Size

    Charles Esquibel

      Hello,

       


      I've loaded an 875x24 table using multiple noconcatenate loads and joins, however when I attempt to do analysis on the table, it gives the wrong number of rows and the analysis is skewed.

      Do you know why this may be happening and how to remove these ghost rows?

       

      Thank you!

      Charlie

       

      Edit: Final solution

      Instead of using a join to merge tables in which the field they share contains multiple of the same value, I ultimately used applymap to 'vlookup' the values and add them to the final table.  Thank you for your help!

       

      Map1:
      Mapping LOAD
      [Task Match],
      MaxCDate
      Resident TableD;

      Map2:
      Mapping LOAD
      [Task Match],
      MaxDue
      Resident TableD;

      Map3:
      Mapping LOAD
      [Task Match],
      ECCheck
      Resident TableD;

      FinalMapped:
      LOAD *,
      ApplyMap('Map1',[Task Match],Null()) as MaxCDate,
      ApplyMap('Map2',[Task Match],Null()) as MaxDue,
      ApplyMap('Map3',[Task Match],Null()) as ECCheck
      Resident NewTable;
      DROP Tables TableD, NewTable;
      EXIT Script

        • Re: Count of Rows in Table does not match Table Size
          Krishnapriya Arumugam

          Hi,

           

          Hope your joins made the increase in row count. Share the application or data model to dig more into the issue

            • Re: Count of Rows in Table does not match Table Size
              Charles Esquibel

              Hello,

              Here is my script... I'm new to using qlik so it may not be exactly optimized.

              InvTable:
              LOAD
              *,
              if([StateRef]=3,'Closed',Today()-[Date Opened]) as [Current Age],
              if([StateRef]=3,[Date Closed]-[Date Opened],'See Current Age') as [Total Age]
              ;
              NoConcatenate
              LOAD
              if([Record State]='Opened' or [Record State]='Assessment/Investigation' or [Record State]='FM Approval Request (Plan)' or [Record State]='QA Review (Plan)' or [Record State]='Extension Request',1,
              if([Record State]='CAPA EC Pending' or [Record State]='CAPA Plan Approved',2,3)) as [StateRef],
              [Parent ID] as [Match ID],
              [Record ID] as [Task Match],
              *
              FROM
              [...1.6.17.xlsx]
              (
              ooxml, embedded labels)
              Where Project ='Investigation CRQA';
              DROP Fields [Final Report Received Date],[Initial Report Distributed On],[Completion Date], [Cancelled On], [Task Types];


              //Add associated alternative awareness dates to each investigation record by combining tables
              Left Join (InvTable)
              LOAD
              [Record ID] as [Match ID],
              [Final Report Received Date],
              [Initial Report Distributed On]

              FROM
              [...1.6.17.xlsx]
              (
              ooxml, embedded labels)
              Where Project = 'Observation CRQA';

              //Add calculated awareness field (Fixed Awareness)
              NewTable:
              Load
              *,
              if([Issue Awareness Date]>0,[Issue Awareness Date],
              if([Initial Report Distributed On]>0,[Initial Report Distributed On],[Final Report Received Date])) as [Fixed Awareness]
              Resident InvTable;
              DROP Table InvTable;
              NoConcatenate
              //Fixing Completion Date from Tasks and assigning a reference value for when there is EC
              TaskTable:
              LOAD
              [Parent ID] as [Task Match],
              if([Completion Date]>0,[Completion Date],if([Cancelled On]>0,[Cancelled On],'1/1/3000')) as [FixedCD],
              IF([Task Types]= 'Correction','0',If([Task Types]='CA','1',If([Task Types]='PA','2',IF([Task Types]='EC','3','2.5')))) as FixedTT,
              [Due Date]
              FROM
              [...1.6.17.xlsx]
              (
              ooxml, embedded labels)
              Where Project ='Task CRQA';
              NoConcatenate
              //Determine last completion date for related tasks
              TableB:
              LOAD
              *,
              If([Task Match]= Previous([Task Match]),Peek('MaxCDate'), [FixedCD]) as MaxCDate
              Resident TaskTable
              Order By [Task Match], [FixedCD] desc;
              DROP Table TaskTable;
              //Determine latest due date for related tasks

              NoConcatenate
              TableC:
              LOAD
              *,
              If([Task Match]= Previous([Task Match]),Peek('MaxDue'), [Due Date]) as MaxDue
              Resident TableB
              Order By [Task Match], [Due Date] desc;
              DROP Table TableB;

              //Determine if a parent record has EC
              NoConcatenate
              TableD:
              LOAD
              [Task Match],
              [FixedCD],
              MaxCDate,
              MaxDue,
              FixedTT,
              If([Task Match]= Previous([Task Match]),Peek('ECCheck'), [FixedTT]) as ECCheck
              Resident TableC
              Order By [Task Match], [FixedTT] desc;
              Drop Table TableC;

              //put the task information together with investigation information
              Left Join (NewTable)
              LOAD
              *
              Resident TableD;
              Drop Table TableD;
              NoConcatenate
              //Add some additional calculated columns
              FinalTable:
              LOAD
              *,
              If([Fixed Awareness]>0,
              If(Floor([Date Opened]-[Fixed Awareness])>=0,Floor([Date Opened]-[Fixed Awareness]),'NA'),'Unaware') as [Alert to Open],
              If("Plan Approved On [QA]">0,Floor("Plan Approved On [QA]"-[Date Opened]),'Not Approved') as [Open to Approval],
              If([Correction, CA, PA Closed On]>0,Floor([Correction, CA, PA Closed On]-"Plan Approved On [QA]"),'Not Completed') as [Approval to Task Completion],
              If(ECCheck=3,
              If([Date Closed]>0,Floor([Date Closed]-[Correction, CA, PA Closed On]),'Pending EC'),'No EC') as [Task to EC Complete],
              If("Initial Approval On [QA]">0,"Initial Approval On [QA]"-[Due Date],Today()-[Due Date]) as [Open Timeliness vs Due Date],
              If([Correction, CA, PA Closed On]>0,[Correction, CA, PA Closed On]-MaxDue,Today()-MaxDue) as [Pending Timeliness vs Due Date]

              Resident NewTable;
              DROP Table NewTable;

            • Re: Count of Rows in Table does not match Table Size
              Karthikeyan Subramaniam

              Your join is having issues. cross check their relationships.

              • Re: Count of Rows in Table does not match Table Size
                Charles Esquibel

                Instead of using a join to merge tables in which the field they share contains multiple of the same value, I ultimately used applymap to 'vlookup' the values and add them to the final table.  Thank you for your help!

                 

                Map1:
                Mapping LOAD
                [Task Match],
                MaxCDate
                Resident TableD;

                Map2:
                Mapping LOAD
                [Task Match],
                MaxDue
                Resident TableD;

                Map3:
                Mapping LOAD
                [Task Match],
                ECCheck
                Resident TableD;

                FinalMapped:
                LOAD *,
                ApplyMap('Map1',[Task Match],Null()) as MaxCDate,
                ApplyMap('Map2',[Task Match],Null()) as MaxDue,
                ApplyMap('Map3',[Task Match],Null()) as ECCheck
                Resident NewTable;
                DROP Tables TableD, NewTable;
                EXIT Script