Concatenate load reduces number of rows in table

[derekjones]

Hi All

I have a curious issue. I'm doing a concatenate load of different facts into one table, however on the Script Excecution dialog box where it gets to add 'CustXRef' to the table the actual number of rows goes down from 2,586,681 to 2,431,000.

Is this actually possible, I can't see how concatenating another table would reduice the overall number of rows in the table it's being added to?

Is this just a bug in the execution dialog box or do I need to investigate further?

My thanks in advance for any assistance.

Derek

[derekjones]

OK, I've got an update if anyone's interested,

To try and analyse what records are being dropped, I put in two store qvd instructions in the load script (one after StockLC is concatenated into Relationships and one after CustXRef).

Interestingly the number of records in the first store is not the same as the lines fetched in the script above (it's lower) and the second qvd after CustXref does increase the rows added to the table by around 150,000.

So is this a bug in the script execution dailaog box or am I misinterpreting what it's saying?

My thanks again for any advice.

Thanks

Derek

[#3]

Hi,

     have  you used trace keyword to see the No of records of  each table in the Dialog .

Just use

Let v=NoOfRows('StockLC');

trace $(v)

and same for CustXRef

lets see what it shows in the Dialog .

//Yusuf

[msteedle]

Is your CustXRef load a load DISTINCT? Concatenating rows with a load distinct can reduce the number of rows in the table. Blogged about here: http://blog.axc.net/?p=1062

There's another post about logging rows that may help you understand what's happening: http://blog.axc.net/?p=1115

[derekjones]

thanks yusuf.ali

OK, I've put in the trace's and below are the results (please note number of records has changed from original above as source files have changed).

As you can see, StockLC has 722,788 records and CustXRef has 159,021.

What's interesting however is that I also did the same for couting records in teh Relationship table after it concat loaded these. After StockLC load there was a total of 2,901,273 records and after CustXRef this jumped to 3,058,430 making it 157,157 records uplift compared to 159,021 recorded in the script.

Not sure what to make of this then, the script says number of rows goes down when it certainly doesn't?

[derekjones]

Hi Michael

I'm not using DISTINCT on concatenating the CustXRef. As my above reply shows, the row count is in fact increasing after the concat of CustXref, the log is saying otherwise?

Thanks for your suggested links.

Derek

[amars]

Hi Derek,

I feel the simplest way to check the no of rows is to go into the Documents Properties -> Tables

and to check for the no of rows.

[sebdupont]

Hi all,

I'm sorry to go back to this old subject, but how is it possible to count lines of a table that is concatenated

Here, how is it possible to have StockLC  records and CustXRef record instead of Relationships records.

How is the script structure ?

StockLc:

Concatenate (Relationships)

Load .....

Let v=NoOfRows('StockLC');

trace $(v)

CustXRef:

Concatenate (Relationships)

Load .....

Let v=NoOfRows('CustXRef');

trace $(v)

Don't work for me always have Relationships records with new records add

Can someone help me ?

[msteedle]

In your solution, you are referring to tables that don't exist in your NoOfRows functions.

At the bottom of this post, I outlined a subroutine you can use for logging row counts, including the delta: http://www.axisgroup.com/data-industry-insights-blog/logging-row-counts

A simpler solution for your example would be to add a hardcoded field to each load, saying what the source is, then you can easily do counts in the front end. Ex.

     'StockLC' as [Source Data],

[sebdupont]

I do not understand how derek gets there in his example

A simpler solution for your example would be to add a hardcoded field to each load, saying what the source is, then you can easily do counts in the front end. Ex.

     'StockLC' as [Source Data],

can you explain please ?

At the bottom of this post, I outlined a subroutine you can use for logging row counts, including the delta: http://www.axisgroup.com/data-industry-insights-blog/logging-row-counts

I tried with no sucess, always a count of Relationships.