Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
Contributor

data load editor: select & load

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!

1 Solution

Accepted Solutions
suepenick
Contributor

Re: data load editor: select & load

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.

10 Replies
MVP
MVP

Re: data load editor: select & load

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
Contributor

Re: data load editor: select & load

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
Valued Contributor

Re: data load editor: select & load

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
Contributor

Re: data load editor: select & load

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
Valued Contributor

Re: data load editor: select & load

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
Contributor

Re: data load editor: select & load

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
Contributor

Re: data load editor: select & load

True

alip_nath
Contributor

Re: data load editor: select & load

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
Contributor

Re: data load editor: select & load

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.

Community Browser