Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi All,
is it possible to add an sql sub query when loading a table from a resident?
i have 2 very big files that i need to join.
i need to load the first one and reduce the number of records and then usethe reduced size table to do a join on the second file.
left join wont work since i must use having statement in the first SQL load,so if i try to add a left join it happens before the number of records isreduced by the having statment.
i would like to load table with sql and then load that same table asresident and add a sub query to another fie.
is it possible?
i hope i explained myself correctly.
this is the load from the first file
TBL1:
load
a, b, c
sql select a, b, sum(c) as c from file1
group by a,b
having sum(c) > 0
now i need to add data from file2 using fileds a and b from TBL1
if I do it in the same sql statement (left join or inner join) the scriptget stuck due to the size of the files.
And i dont want to load the entire file2 as a table because it huge.
Any suggestions?
Hello
Have you tried doing something like:
TBL1:
load
a, b, c, a&b as key
sql select a, b, sum(c) as c from file1
group by a,b
having sum(c) > 0;
INNER JOIN
load
d, e, f, e&f as key
sql select d,e,f from file2;
Think that should work for you but not sure about order of load, think it will execute first load fully (with the having clause) and then join to the second load. the "key" field is the field that QlikView will use to join on.
Good luck,