topic Concatenate is creating records in QlikView

Concatenate is creating records

Fri, 18 Jun 2010 21:25:34 GMT

In theory when I concatenate two tables the total of rows will be the sum of the rows in each table but that is not what is going on with my script.

If I load the tables separely (in different QVW files), this is the number of rows I get, which is correct.

# of rows in table 1: 37,690,284
# of rows in table 2: 15,215,949

After I join the tables, this is what I get, which is absolutely wrong.

# of rows in table 1: 37,690,284
# of rows in table 2: 52,906,233

Does anybody knows what could be wrong?

This is my script:
Load
Key1
, Key2
, Key3
, Amount
, Quantity
From myQvd1.qvd(qvd);

Concatenate

Load
Key1
, Key2
, Key3
, Amount
, Quantity
From myQvd2.qvd(qvd);

Concatenate is creating records

johnw — Fri, 18 Jun 2010 22:09:15 GMT

37,690,284 + 15,215,949 = 52,906,233

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.

Concatenate is creating records

Fri, 18 Jun 2010 22:13:27 GMT

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

Concatenate is creating records

Fri, 18 Jun 2010 22:15:32 GMT

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.

Concatenate is creating records

Fri, 18 Jun 2010 22:17:49 GMT

Umang,

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.

Concatenate is creating records

Fri, 18 Jun 2010 22:58:27 GMT

Any idea guys?

Concatenate is creating records

johnw — Fri, 18 Jun 2010 23:07:31 GMT

Nope. Some bug somewhere, probably in your code, possibly in QlikView itself. Can't say other than that.

Concatenate is creating records

Anonymous — Sat, 19 Jun 2010 03:32:23 GMT

Igor,

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

Concatenate is creating records

wizardo — Sun, 20 Jun 2010 10:26:22 GMT

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

table B 1354 rows fetched

Mansyno

Concatenate is creating records

Mon, 21 Jun 2010 17:07:43 GMT

Michael,

The problem is not actually the # of rows, is the calculation that is wrong, it is giving me more than it should.

Concatenate is creating records

Mon, 21 Jun 2010 17:09:32 GMT

Michael,

Regarding to the Year, one of my Key columns is Year, I renamed them here in this post to protect my customer's data structure.

Concatenate is creating records

Mon, 21 Jun 2010 17:55:31 GMT

Check carefully your data and logic. Try to make a calculation manually from your QV data. I'm pretty sure you will find a cause of the problem.

Concatenate is creating records

Mon, 21 Jun 2010 18:07:04 GMT

It is working now, but I cannot explain why.

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.

Thank you all for your help.