topic Re: How many tables are created when I load a script with two tables having same fields? in QlikView

How many tables are created when I load a script with two tables having same fields?

Wed, 02 Dec 2015 01:12:46 GMT

I've the following script. How many tables will be there in the schema when I load the script?

GeneralStore1:

LOAD Emp_ID,

Name,

SalaryLimit,

Estd_date,

ContactAddr;

SQL SELECT Emp_ID,

Name,

SalaryLimit,

Estd_date,

ContactAddr

FROM first_store;

GeneralStore2:

LOAD Emp_ID,

Name,

SalaryLimit,

Estd_date,

ContactAddr;

SQL SELECT Emp_ID,

Name,

SalaryLimit,

Estd_date,

ContactAddr

FROM second_store;

I was thinking that there will be two tables with synthetic key formed inbetween, or maybe two tables with three fields connected. But my assumption was wrong and I was told that there will only be one table with the name GeneralStore1 in the schema.

Can someone please tell why would it be so??

Re: How many tables are created when I load a script with two tables having same fields?

vikasmahajan — Wed, 02 Dec 2015 07:24:55 GMT

Qlikview concatenate automatically , if fields are same . Hence you got only one table.

Vikas

Re: How many tables are created when I load a script with two tables having same fields?

Sun, 06 Dec 2015 16:16:21 GMT

There is a lot of explanation about concatenation out there. In short, the table GeneralStore 2 are sticked "underneath" the GeneralStore1 with the fields in the same order. So if you have two names "A" & "B" in the first table, and two names "A" & "C" in the second table. You will get one table with the names "A","B", "A", "C".

What result are you looking for?

Re: How many tables are created when I load a script with two tables having same fields?

maxgro — Sun, 06 Dec 2015 17:01:38 GMT

- if you have the same number and names of fields Qlik will concatenate the 2 tables; this happened with your script; you get one table, the first you load

- if you don't want this use noconcatenate; add noconcatenate to the second table load, you'll get a syn key

- if you want to concatenate tables with different number and/or names of fields use concatenate

From Qlik help

NoConcatenate

The NoConcatenate prefix forces two loaded tables with identical field sets to be treated as two separate internal tables, when they otherwise would be automatically be concatenated.

The syntax is:

noconcatenate ( loadstatement | selectstatement )

Example:

Load A,B from file1.csv;

noconcatenate load A,B from file2.csv;

Concatenate

If two tables that are to be concatenated have different sets of Fields, Concatenation of two tables can still be forced with the Concatenate prefix. This statement forces concatenation with an existing named table or the latest previously created Logical Table. A concatenation is in principle the same as the SQL UNION statement, but with two differences: first that Concatenate prefix can be used no matter if the tables have identical field names or not; and secondly that no removal of identical records are made. When DISTINCT predicate is used in a LOAD statement, the resulting table becomes distinct. Any data added to the table also becomes distinct, regardless if the data is concatenated or joined.

The syntax is:

concatenate [ (tablename ) ] ( loadstatement | selectstatement )

Examples:

Concatenate LOAD* FROM file2.csv;

Concatenate SQL SELECT * FROM table3;

tab1:

Load * from file1.csv;

tab2:

load * from file2.csv;

.. .. ..

Concatenate (tab1) load * from file3.csv;

Re: How many tables are created when I load a script with two tables having same fields?

Mon, 07 Dec 2015 00:57:38 GMT

Because of Auto concatenate concept in qlikview you will get one table as Vikas said..

if you do not want one table you can use NOconcatenate

Re: How many tables are created when I load a script with two tables having same fields?

HirisH_V7 — Mon, 07 Dec 2015 04:08:00 GMT

Hi,

When we load two same tables. qlikview automatically creates full outer join and Creates a single table if the fields are same or else if fields are different unmatched records will get appended automatically in new single table.

T:


LOAD * INLINE [
    ID, Name
    1, Mike
    2, Nik
];


T2:


LOAD * INLINE [
    ID, Name
    1, Mike
    2, Nik
];

If we load this table we will get a result like this,

Hope this helps ,

Regards,

HirisH

Re: How many tables are created when I load a script with two tables having same fields?

jonathandienst — Mon, 07 Dec 2015 05:05:08 GMT

Lets correct some misconceptions.

Qlikview will concatenate (not join) if the two tables have the same list of fields. Hiris, a table box does not display duplicates, so you only see two rows, but if you count the rows or check the table viewer, you will see 4 rows.
You can force concatenation using the concatenate keyword. If you concatenate tables containing A, B and A,C the result table will contain A, B, C, (not A, B , A, C) - with nulls for the missing values of C and B respectively.
Qliview can and does store duplicate rows - it is not a normal form database.

Re: How many tables are created when I load a script with two tables having same fields?

Mon, 07 Dec 2015 07:22:05 GMT

NIce explanation Jonathan

Re: How many tables are created when I load a script with two tables having same fields?

Mon, 07 Dec 2015 21:34:45 GMT

"You can force concatenation using the concatenate keyword. If you concatenate tables containing A, B and A,C the result table will contain A, B, C, (not A, B , A, C) - with nulls for the missing values of C and B respectively."

?????????????????????????????

Either you get duplicates i one field or duplicates split on two fields depending on (re)naming of the fields.