I simply create another column called Balance. The 1st load I said 'Current Year' As Balance. In the 2nd load I said 'Prior Yer' As Balance.
Now and I do my year over year comparation, I use Set Analysis to indentify which Year I want:
Sum({$<Balance={'Prior Year'}>} Amount)
I also checked the data. Prior Year is only 2009, non 2010. Current Year is always 2010, none 2009. So I do not understand why my workaround made that work, but it is working.
What do you mean by "join the tables"? If you are concatenating, you should be making ONE table, not two, and you shouldn't need to join them. The script you show should be making ONE table. I don't know what you're talking about when you give a count of rows in two different tables after you should have been building only one table. Is that your problem? You're building two tables when it should be one? I really don't think I understand your question.
Maybe the values in table two are different than table one and hence a concatenate would just add rows in the joined table as matching values may not be the same
Yes, I expect one table as the result. Let me explain a little better.
Table1 has 2009 data, table2 has 2010 data. If I load only 2009 the results of my charts are correct for 2009. If I load only 2010 the results are also correct for 2010. However if I load both, the results are correct for 2010 but wrong to 2009, almost double of what should be.
I believe the Concatenate does not try to match values, it just adds the second table in the bottom of the 1st, which is exactly what I want. The function that does this matching is the JOIN.
I don't see anything wrong in this part of code. Apparently concatenate creates one table, and the only place where you can see the number of rows of the "first table" is the log file. But notthing is wrong here either. You load first 37+ mln rows from QVD1, and log file shows this number, and append another 15 + mln rows from QVD2, and log file shows the resulting number. If something is wrong - it is in the overall data model and/or the chart. None of the fields in the script above implies the date (2009 or 2010), so I can't see how you address this in chart.
The bottom line - concatenate does not create records. (And, concatenate is not join - it's more like union.)
i think the confusion comes from the way the log window shows the count of rows it reads. when it reads from a table that gets concatenated to a table previously loaded, the log window does not show how many rows it read from the 2nd table, it shows the combined amount.
for example. if table A has 1000 rows and table b has 354 rows, the log will show this: table A 1000 rows fetched
