data load editor: select & load

[wanyunyang]

Hi guys,

I come across I problem I don't understand:

In my data load editor, I have table1 with fields A, B, table2 with fields A, C. My script is:

TABLE:

SELECT A, B FROM table1;

LEFT JOIN (TABLE)

SELECT A, C FROM table2;

LOAD A,B,C RESIDENT TABLE;

But everything is duplicated. If I remove the LOAD, everything is correct. I don't quite understand why. Any idea? Is there any reference regarding this?

Thanks for help!

[petter]

The LOAD statement will not create a new table as all the fields are similar to the table called TABLE. This is called auto concatenation in Qlik. So you will get a duplication of all the rows.

Here it is explained in the documentation: https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/LoadData/concatenate-tables.ht...

What are you trying to achieve by having the LOAD statement there?

[suepenick]

File1:

Load

A

B

From...

File2:

left join (File1)

Load

A

C

From...

You don't need to reload to see the result which would be

File 1

A

B

C

if you do reload Resident

Drop Table File1 (or you will have 2 sets of the same data)

[dwforest]

This would autoconcatenate at Peter pointed out.

if you do need to load another copy and don't want it to concatenate, use NoConcatenate

[wanyunyang]

I thought the SELECT query only does selection from database, so there should be a LOAD afterwards to load desired fields into QLIK SENSE.

So the fact is both SELECT query and LOAD will load fields into QS?

[dwforest]

Sort of

Depends on the location and order of the statements.

If no further manipulation of the data is needed, SELECT on its own is fine.

If you need to create a calculated dimension, then the LOAD goes above the SELECT, e.g.

LOAD A+B AS C;

SELECT A, B FROM TABLE1;

see Help

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/load-data-from-previ...

[wanyunyang]

If I do sth like:

table1:

SQL SELECT OrderDate FROM...;

LOAD Date#(OrderDate,’YYYYMMDD’) as OrderDate RESIDENT table1;

I still need to drop table1 to avoid duplication, right?

[suepenick]

True

[Olip]

Hi Wanyun,

There are a few points that perhaps will help you think through your requirement:

1. The  join statement in general does a full outer join i.e all rows from both the tables. If your duplicates are due to common rows between the 2 tables , you better use right ,left or inner join per your need
2. Auto-concatenate of Tables: by Default if subsequent loads have common columns then Qlik will concatenate the table. In your above example you do not need the third statement 'LOAD A,B,C RESIDENT TABLE;' . The first two statements should suffice. You just need to take care of the appropriate 'Join' as mentioned in Point 1
3. If you need to have subsequent logic and then load the data into a different table you need to proceed the load with Noconcatenate statement. Once loaded the previous tables needs to be dropped else they will create synthetic keys

Hope this helps.

Check this link for info on join:

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/combine-tables-join...

-Alip

[suepenick · Accepted solution]

I see the confusion.  if you do a Load first of the fields, fix their names, fix dates

then the select to go get the fields you need.   You don't need to reload the file again.

MyInfo:

Load

A as OrderNo,

B as InvoiceNo,

C as QtyShipped,

Date( Date#( InvoiceDate, 'YYYYMMDD'), 'MM/DD/YYYY') as InvoiceDateWithDashes;

Select

A,B,C,InvoiceDate

from data.dbo.MyFile  with (nolock)

where InvoiceDate > 20170101;

select * brings all the fields, name them in the select statement and you only bring the ones in you want

If you are loading from Excel or QVD you don't need the select statement at all

     I use reloading after I have left joined a multiple of tables together to clean up any field dimensions missing because of the joins.  in the load Resident use NoConcatenate; clean up fields  if(isnull(Field),' ',Field) as FieldName - then drop the original joined-to-death table.  You will find clean results in pivot tables.