topic Re: Where Clause in Load Editor for linked tables in App Development

Where Clause in Load Editor for linked tables

pascaldijkshoor — Fri, 04 May 2018 14:07:50 GMT

Hi guys,

I am loading two different tables in the load editor. I have a where clause on the first table, which is applied on a datefield. The second table is linked to the first table by a mutual column. The second table has no date field, so currently it's loading all the data, which is 18 million rows. For minimizing my dataset I want to apply the where clause of the first table on the second table as well.

Is this possible?

I am currently loading the following script:

IB CONNECT TO 'Chainware11g';

LOAD cuacty,

cuactk as Key,

cuconn as Company_Lane,

cudura,

tsaaed,

tsloai;

[cuf_cuactt]:

SELECT "cuacty",

"cuconn",

"cudura",

"tsaaed",

"tsloai",

"cuactk"

FROM "ICJDR"."cuf_cuactt" WHERE "tsaaed" >= sysdate - '65';

LIB CONNECT TO 'Chainware11g';

LOAD cuactk as Key,

cuasst,

jrasst as Truck_Number;

[cuf_cuacta]:

select "cuactk",

"cuasst",

"jrasst"

FROM "ICJDR"."cuf_cuacta";

Re: Where Clause in Load Editor for linked tables

Anonymous — Fri, 04 May 2018 21:50:56 GMT

Yes, Pascal, you'd simply add a WHERE EXISTS clause in your second load statement. So something like this:

LOAD cuactk as Key,

cuasst,

jrasst as Truck_Number

WHERE EXISTS(Key,cuactk);

[cuf_cuacta]:

select "cuactk",

"cuasst",

"jrasst"

FROM "ICJDR"."cuf_cuacta";

You can find more information on the EXISTS clause here: https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/InterRecordFunctions/Exists.htm

And the WHERE clause here: https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Load.htm

I hope that helps,

Serina

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Re: Where Clause in Load Editor for linked tables

pascaldijkshoor — Mon, 07 May 2018 07:30:12 GMT

Thanks for your reply, but unfortunately the script did not work.

Re: Where Clause in Load Editor for linked tables

marcus_sommer — Mon, 07 May 2018 12:32:27 GMT

What didn't work? Any load-error (probably some small syntax-issue) or had the second query more and/or different data loaded as expected? In the last case your Key field is just inappropriate to your target and I think it would be better to join both tables with your where-clause on the sql-side.

- Marcus

Re: Where Clause in Load Editor for linked tables

pascaldijkshoor — Mon, 07 May 2018 13:05:05 GMT

Hi Marcus, the script did not work because of the following load error: missing SELECT keyword (see the corresponding script below)

How can I join both tables with my where clause? I already tried to do this but i'm no programmer so it's difficult for me to achieve this.

LIB CONNECT TO 'Chainware11g';

LOAD cuacty,

cuactk as Key,

cuconn as Company_Lane,

cudura,

tsaaed,

tsloai;

[cuf_cuactt]:

SELECT "cuacty",

"cuconn",

"cudura",

"tsaaed",

"tsloai",

"cuactk"

FROM "ICJDR"."cuf_cuactt" WHERE "tsaaed" >= sysdate - '65';

LIB CONNECT TO 'Chainware11g';

Load cuactk as Key,

cuasst,

jrasst as Truck#;

[cuf_cuacta]:

select "cuactk",

"cuasst",

"jrasst"

FROM "ICJDR"."cuf_cuacta" WHERE EXISTS (Key, cuactk);

Re: Where Clause in Load Editor for linked tables

marcus_sommer — Mon, 07 May 2018 13:58:57 GMT

I think it should go in this direction:

LIB CONNECT TO 'Chainware11g';

[cuf_cuactt]:

LOAD

cuacty, cuactk as Key, cuconn as Company_Lane, cudura, tsaaed, tsloai, cuasst, jrasst as Truck#;

SELECT "a.cuacty", "a.cuconn", "a.cudura", "a.tsaaed", "a.tsloai", "a.cuactk", "b.cuasst", "b.jrasst"

FROM "ICJDR"."cuf_cuactt" a left join "ICJDR"."cuf_cuacta" b on "a. cuactk" = "b. cuactk"

WHERE "a.tsaaed" >= sysdate - '65';

whereby the SQL statement depends on the syntax-rules from your database and you might need some google-searching to adjust it appropriate (surely there are various examples on how to join and alias tables).

- Marcus

Re: Where Clause in Load Editor for linked tables

pascaldijkshoor — Tue, 08 May 2018 08:56:57 GMT

Thanks Marcus! With some small changes I managed to get the script to work