Look up "Automatic Concatenation" in the reference manual. If you load two tables with the exact same field structure, which is the case when doing a resident load without changing anything and just using the star, they will automatically be concatenated.
So if you have Table A with 1000 rows and do a resident load using Load * Resident TableA; you'll end up with Table A but with 2000 rows. A duplicate now exists for every row. In order to stop QlikView from automatically concatenating tables you can prefix the load statement with: NOCONCATENATE
You r rite John. But my query is bit different. In my case, both tables have only some fields incommon and others can be different. I want to know why * is not behaving properly and simply creating a junk field with *, all works perfectly.
You have two tables called Productivity_GSMCShared and Productivity_FG. They contain different fields and are different tables in QlikView.
Then you want to create a new table called Productivity where you load all the fields and rows from the two tables and concatenate them into one table?
The first part:
Load * Resident Productivity_GSMCShared; will automatically be concatenated to the old Productivity_GSMCShared and Productivity_FG and no table will be created. Same thing with the other load, resident Productivity_FG.. it will be concatenated automatically with its parent table. That is why the second approach works.. since the tables you load resident are different from their parents.
I'm assuming you're going to drop the parent tables after concatenating the resident loads anyway so add the "dummy" fields in the parent tables instead and then list the fields you want from the parents in the resident loads instead of using the *.
I got the key understanding that lies behind Concatenate.
Need one favor. I am basically from Microsoft programming background and working on QV from last one year. I want to know which technology is there behind QV, how you achived like - data n application both in RAM, around 80-90% reduction on data storage by storing only one occurrance of a particular value in one column, and many others.
Moreover, like other programming language as well as database language, is there any catalogue readily available for all the syntax that can be used in script editor as well as in chart expressions.
eg. I know Load, i know concatenate but i was not aware abt Concatenate ([Productivity]) that explicitly tells that concatenate into [Productivity] table only. In the same way, i saw NoConcatenate somewhere but did not know abt its exact behavior.
I know you will ask me to check ApiGuide. But that doesn't serve my purpose. eg. i need syntax like -
SELECT [ DISTINCT | ALL ] column_expression1, column_expression2, .... [ FROM from_clause ] [ WHERE where_expression ] [ GROUP BY expression1, expression2, .... ] [ HAVING having_expression ] [ ORDER BY order_column_expr1, order_column_expr2, .... ]
column_expression ::= expression [ AS ] [ column_alias ]
from_clause ::= select_table1, select_table2, ... from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr ... from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr ... from_clause ::= select_table1 [INNER] JOIN select_table2 ...
select_table ::= table_name [ AS ] [ table_alias ] select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]
order_column_expr ::= expression [ ASC | DESC ]
Above is the Select statement syntax with SQL. It states syntax in full with all possible combinations and options that can be used with Select.
Instead of looking at the API guide I would suggest using the F1 help. Whenever I run into syntax issues when scripting I hit F1 and look up the function I'm working with.
It brings up something similar to what you describe, the full syntax and a breakdown of the different operators and a few examples.
Well, while the API guide is sometimes necessary, the most useful for me has been the F1 help, as Johannes suggested. I'm always looking things up there.
I'm not sure quite what you're asking about the technology behind QlikView. While I can say that QlikView appears to compress the data in memory fairly efficiently, I can't tell you exactly how they go about it. In any case, in a typical application, data storage just isn't a concern of mine because the compression does its job. On those rare occasions it's a problem, I'll create a .mem file and have a look at what's taking the space.
Right there in the first load statement is a problem with automatic concatenation. You can't Load * Resident anytable; without causing an automatic concatenation situation. The rows are loaded, but not into the Productivity table, which does not get created, but they are loaded into the Productivity_GSMCShared table where they appear as duplicate rows.
