Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

preceding load with concatenated tables load

I am combining several tables into one and need to filter on one field together with making sure values are unique.

I thought about using the preceding load and came up with something like below:

LOAD DISTINCT *

WHERE field3='A';

LOAD

field1,

field2,

field3;

SQL SELECT * FROM table1;

CONCATENATE LOAD

field1,

field2,

field3;

SQL SELECT * FROM table2;

CONCATENATE LOAD

field1,

field2,

field3;

SQL SELECT * FROM table3;

It is not working though, because all values in field3 are still available.

What am I missing out?

thanj you!

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

I may be mistaken, but I think you can do it like this:

LOAD Distinct

field1, 

field2, 

field3; 

SQL SELECT * FROM table1

WHERE field3='A';

 

CONCATENATE

LOAD Distinct

field1, 

field2, 

field3; 

SQL SELECT * FROM table2; 

WHERE field3='A';

 

CONCATENATE

LOAD Distinct

field1, 

field2, 

field3; 

SQL SELECT * FROM table3; 

WHERE field3='A';

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

11 Replies
nagaiank
Specialist III
Specialist III

Preceding load will not work in this case. Try the following:


TABLE1: 
LOAD field1,  field2,  field3; 
SQL SELECT * FROM table1; 

CONCATENATE LOAD field1,  field2,  field3; 
SQL SELECT * FROM table2; 
CONCATENATE LOAD field1,  field2,  field3; 
SQL SELECT * FROM table3;

TABLE2:
NOCONCATENATE
LOAD DISTINCT * RESIDENT TABLE1
WHERE field3='A';

Not applicable
Author

that is indeed working, but I wanted to avoid the extra step of creating a temporary resident table

instead I was trying to use the full benefit of a preceding load; I was under the impression that you could achieve concatenation and use a preceding load

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

I may be mistaken, but I think you can do it like this:

LOAD Distinct

field1, 

field2, 

field3; 

SQL SELECT * FROM table1

WHERE field3='A';

 

CONCATENATE

LOAD Distinct

field1, 

field2, 

field3; 

SQL SELECT * FROM table2; 

WHERE field3='A';

 

CONCATENATE

LOAD Distinct

field1, 

field2, 

field3; 

SQL SELECT * FROM table3; 

WHERE field3='A';

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Clever_Anjos
Employee
Employee

Preceding LOAD works with next command only.

In your case, you have three distinct commands.

You can rewrite your SQL like this

LOAD

field1,

field2,

field3

WHERE  field3='A';

SQL

SELECT field1, field2,field3; FROM table1;

UNION

SELECT field1, field2,field3; FROM table2;

UNION

SELECT field1, field2,field3; FROM table3;

rbecher
MVP
MVP

Good approach to let the database do the distinct by UNION (typos although) but you have to filter in the database or use DISTINCT in LOAD because of there could be still duplicates in field1/field2 combinations:

LOAD field1, field2, field3;

SQL

SELECT field1, field2, field3 FROM table1 WHERE field3='A' 

UNION  

SELECT field1, field2, field3 FROM table2 WHERE field3='A' 

UNION  

SELECT field1, field2, field3 FROM table3 WHERE field3='A';

- Ralf

Astrato.io Head of R&D
Clever_Anjos
Employee
Employee

You´re right Ralf Becher (as usual ).

And your solution performs better too.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

As others have pointed out, it would probably be best to do the where filtering in the SQL. However, to answer the question about preceeding load with concatenate. You can do it, but you must include the preceding load on each load.

LOAD DISTINCT * 

WHERE field3='A'; 

 

LOAD 

field1, 

field2, 

field3; 

SQL SELECT * FROM table1; 

LOAD DISTINCT * 

WHERE field3='A';

CONCATENATE LOAD 

field1, 

field2, 

field3; 

SQL SELECT * FROM table2; 

The final table will be fully distinct.

-Rob

Anonymous
Not applicable
Author

I am usually going with QVD approach if I have multiple tables to create one table. In your case, 

TABLE: 

LOAD field1,  field2,  field3; 

SQL SELECT * FROM table1;

CONCATENATE

LOAD field1,  field2,  field3; 

SQL SELECT * FROM table2; 

CONCATENATE

LOAD field1,  field2,  field3; 

SQL SELECT * FROM table3;

STORE TABLE into TABLE.QVD;

DROP Table TABLE;

TABLE:

LOAD field1,  field2,  field3

FROM

TABLE.QVD

(qvd) Where field3='A';

Not applicable
Author

so many inputs, thank you guys.

In the end I already resolved it as Rob did.

Rob Wunderlich wrote:

As others have pointed out, it would probably be best to do the where filtering in the SQL. However, to answer the question about preceeding load with concatenate. You can do it, but you must include the preceding load on each load.

Indeed this is true.

The funny thing is that I had to filter on a preceding load, because filtering in the SQL would make the load to crash.

I never realised why, maybe some timeout issues?