topic Re: Load a file with sql conditioning to another file in QlikView

Load a file with sql conditioning to another file

mauriziob — Thu, 23 Oct 2014 12:59:47 GMT

Hi, i've to load a file but the records must be conditioning to another load.

Example:

LOAD

A as [key1], B as [CodeFile01_B], C as [CodeFile01_C];

SQL

select A, B, C from lib01.File01;

LOAD

A as [key1], B as [CodeFile02_B], C as [CodeFile02_C], D as [CodeFile02_D], where exists(A);

SQL

select AA, BB, CC, DD from libe02.File02;

The record of File02 must be load if this exist in File01.

Can i make this operation?

If i launch the script there isn't any error but the second load doesn't load any record!

Thanks.

Re: Load a file with sql conditioning to another file

alexandros17 — Thu, 23 Oct 2014 13:05:23 GMT

Change

LOAD

A as [key1], B as [CodeFile02_B], C as [CodeFile02_C], D as [CodeFile02_D], where exists(A);

SQL

select AA, BB, CC, DD from libe02.File02;

LOAD

A as [key1], B as [CodeFile02_B], C as [CodeFile02_C], D as [CodeFile02_D], where exists([key1,A);

SQL

select AA, BB, CC, DD from libe02.File02;

let me know

Re: Load a file with sql conditioning to another file

anbu1984 — Thu, 23 Oct 2014 13:06:00 GMT

Since you renamed your field, you should use alias

LOAD

A as [key1], B as [CodeFile02_B], C as [CodeFile02_C], D as [CodeFile02_D] where exists([key1]);

SQL

select AA, BB, CC, DD from libe02.File02;

Re: Load a file with sql conditioning to another file

mauriziob — Thu, 23 Oct 2014 13:23:15 GMT

Hi, the alias without the old field name don't function! I followed indication of Alessandro. Thanks to all.

Re: Load a file with sql conditioning to another file

anbu1984 — Thu, 23 Oct 2014 13:43:47 GMT

[key1] - Refers to field in first load

A - Refers to field in your second load

LOAD

A as [key1], B as [CodeFile02_B], C as [CodeFile02_C], D as [CodeFile02_D], where exists([key1],A);

You cannot use alias in where if it is created in same Load (Ex: Load A as B From Resident Temp Where B > 1 -- This won't work). My reply didn't work since [key 1] was not present in where clause. You can try like this

Load * where exists([key1]);

LOAD

A as [key1], B as [CodeFile02_B], C as [CodeFile02_C], D as [CodeFile02_D];

SQL

select AA, BB, CC, DD from libe02.File02;