Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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?
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)
This would autoconcatenate at Peter pointed out.
if you do need to load another copy and don't want it to concatenate, use NoConcatenate
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?
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
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?
True
Hi Wanyun,
There are a few points that perhaps will help you think through your requirement:
Hope this helps.
Check this link for info on join:
-Alip
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.