Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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';
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
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
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;
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
You´re right Ralf Becher (as usual ).
And your solution performs better too.
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
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';
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?