topic Re: preceding load with concatenated tables load in QlikView

preceding load with concatenated tables load

Sun, 08 Jun 2014 15:01:28 GMT

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!

Re: preceding load with concatenated tables load

nagaiank — Sun, 08 Jun 2014 15:23:04 GMT

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';

Re: preceding load with concatenated tables load

Sun, 08 Jun 2014 15:36:24 GMT

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

Re: preceding load with concatenated tables load

jonathandienst — Sun, 08 Jun 2014 17:43:02 GMT

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

Re: preceding load with concatenated tables load

Clever_Anjos — Sun, 08 Jun 2014 20:27:33 GMT

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;

Re: Re: preceding load with concatenated tables load

rbecher — Sun, 08 Jun 2014 21:44:40 GMT

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

Re: Re: preceding load with concatenated tables load

Clever_Anjos — Sun, 08 Jun 2014 22:43:13 GMT

You´re right Ralf Becher (as usual ).

And your solution performs better too.

Re: preceding load with concatenated tables load

rwunderlich — Mon, 09 Jun 2014 01:42:31 GMT

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

Re: preceding load with concatenated tables load

Anonymous — Mon, 09 Jun 2014 02:38:40 GMT

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';

Re: preceding load with concatenated tables load

Mon, 09 Jun 2014 09:07:48 GMT

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?

Re: preceding load with concatenated tables load

Clever_Anjos — Mon, 09 Jun 2014 12:40:28 GMT

Maybe filtering into your SQL can lead to a 'full table scan'.

Please contact your Database Administrator and discuss your SQL´s with him analysing the execution plan of each SQL

Re: preceding load with concatenated tables load

Tue, 10 Jun 2014 12:45:31 GMT

I already did so, but bewilderment and no suggestion came only from his side.

I got a tip from a colleague to try with a preceding load and it worked better.

This why my stubbornness to do the above with preceding loads