topic Re: Count of Rows in Table does not match Table Size in QlikView

Count of Rows in Table does not match Table Size

Wed, 11 Jan 2017 15:52:37 GMT

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

krishnacbe — Wed, 11 Jan 2017 16:07:59 GMT

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

Wed, 11 Jan 2017 16:12:25 GMT

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

krishnacbe — Wed, 11 Jan 2017 16:19:12 GMT

In the above script you have two tables finally.

1. Final Tab

2. InvTable

what is the relation between these two tables?

If possible share the application.

Re: Count of Rows in Table does not match Table Size

Wed, 11 Jan 2017 18:39:11 GMT

Going down the script-

Starting File: 3 record types (investigation, task, observation)

- InvTable separates and modifies the investigations records.

- InvTable is left joined with the main data sheet to pull more fields associated with observation records

- NewTable loads from InvTable and adds a modified column and drops the InvTable

- From there TaskTable, TableB, TableC, and TableD are loaded for the modification of columns in task records which are all dropped after joining back into NewTable

- FinalTable loads NewTable and adds calculated columns, and NewTable is dropped leaving FinalTable as the only remaining table in the file.

The final table (FinalTable) contains the expected, correct number of rows as compared to the excel file (875). However, that number is not what the analysis provides (total row count 1615)

I used 'noconcatenate' when loading multiple tables but am at a loss as to why the table appears correct yet has these hidden values.

Re: Count of Rows in Table does not match Table Size

rwunderlich — Wed, 11 Jan 2017 19:02:06 GMT

Can you post a couple of things:

1. A screenshot of your table model (Ctlr-t)

2. A tablebox with $Table and $Rows so we can see the row count of each table.

-Rob

Re: Count of Rows in Table does not match Table Size

Wed, 11 Jan 2017 19:20:15 GMT

Re: Count of Rows in Table does not match Table Size

rwunderlich — Wed, 11 Jan 2017 19:28:06 GMT

So you have 1615 rows, question is why/where in the script. What's the purpose of this self join?

//put the task information together with investigation information
Left Join (NewTable)
LOAD
*
Resident TableD;

I suggest you check the row count of the table(s) at various points in the script and narrow down which join is adding rows. You can check the row count by either:

1. Terminate the script with an

exit script;

statement and look at the table view.

LET vRows = NoOfRows('tablename');

TRACE >> $(vRows);

-Rob

Re: Count of Rows in Table does not match Table Size

krishnacbe — Wed, 11 Jan 2017 19:31:02 GMT

You need to Debug the Script and find out where the rows got increased.

Where you could find that FinalTable has 875 rows?

Re: Count of Rows in Table does not match Table Size

Wed, 11 Jan 2017 19:49:27 GMT

After a bit of searching, the extra rows are in fact being added during this join:

Left Join (NewTable)
LOAD

*
Resident TableD

I'm not sure why this is the case as the two tables only share one common column [Task Match]. How can I add the columns from Table D (Previously marked as *: [Task Match], [FixedCD], MaxCDate, MaxDue, FixedTT,CCheck) to NewTable without the added rows?

Here are the available fields immediately preceeding the join.

Re: Count of Rows in Table does not match Table Size

rwunderlich — Wed, 11 Jan 2017 20:34:36 GMT

You have multiple rows with the same [Task Match], so the join will create additional rows. If all the other field values are identical for each [Task Match], then you should be able ot get away with making TableD distinct. Add the DISTINCT keyword to the creation of TableD.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Count of Rows in Table does not match Table Size

Anonymous — Wed, 11 Jan 2017 21:57:47 GMT

Your join is having issues. cross check their relationships.

Re: Count of Rows in Table does not match Table Size

rahulpawarb — Thu, 12 Jan 2017 08:47:22 GMT

I agree with Rob and Karthikeyan! There are high chances that the join strategy you used in not correct. Request you to debug the script code by breaking into smaller parts/commenting & un-commenting sub parts. Hope this makes sense.

Regards!

Rahul

Re: Count of Rows in Table does not match Table Size

Thu, 12 Jan 2017 16:07:00 GMT