Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

sql sub query from a resident load

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?

1 Reply
Not applicable

sql sub query from a resident load

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,

Community Browser