Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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??

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

8 Replies
vikasmahajan

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

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable
Author

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?    

maxgro
MVP
MVP

- 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;

Not applicable
Author

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

HirisH_V7
Master
Master

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,

2 tables full outer join.PNG

Hope this helps ,

Regards,

HirisH

HirisH
“Aspire to Inspire before we Expire!”
jonathandienst
Partner - Champion III
Partner - Champion III

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.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

NIce explanation Jonathan

Not applicable
Author

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