Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
jontydkpi
Not applicable

Re: preceding load with concatenated tables load

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
11 Replies
nagaiank
Not applicable

Re: preceding load with concatenated tables load

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

Re: preceding load with concatenated tables load

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

jontydkpi
Not applicable

Re: preceding load with concatenated tables load

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
cuv
Not applicable

Re: preceding load with concatenated tables load

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
Not applicable

Re: Re: preceding load with concatenated tables load

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

cuv
Not applicable

Re: Re: preceding load with concatenated tables load

You´re right Ralf Becher (as usual ).

And your solution performs better too.

rwunderlich
Not applicable

Re: preceding load with concatenated tables load

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

satyadev_j
Not applicable

Re: preceding load with concatenated tables load

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

Re: preceding load with concatenated tables load

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?