topic Re: Not Exists in QlikView

Not Exists

rahulsingh12 — Fri, 30 Jun 2017 06:17:40 GMT

I have a table with two ID columns Primary_ID and FK_ID.

The Primary_ID contains the FK_ID also ,so while loading the scipt I want the PrimaryID which are not the FK_ID .

How can I do it at a script level while loading the data.

Is there anything like not exist for script level restriction.

Regards,

Re: Not Exists

Peter_Cammaert — Fri, 30 Jun 2017 06:24:56 GMT

Exists() really is a script level restriction, nothing else.

But your requirement is a bit weird. You say that Primary_ID contains FK_ID, but next you want to load all rows where Primary_ID <> FK_ID... Isn't that a bit contradictory?

Re: Not Exists

rahulsingh12 — Fri, 30 Jun 2017 06:30:39 GMT

Actually It is what the way the day has to be structured.

The Primary may contain FK id but we want only the one's which are not in FK.

Regards,

Re: Not Exists

rahulsingh12 — Fri, 30 Jun 2017 06:36:40 GMT

any suggestion on how to go about it.

basically the table should contain Primary and FK both fields but Primary should not have FK values.

Re: Not Exists

antoniotiman — Fri, 30 Jun 2017 06:40:30 GMT

May be

..... Where Not Exists(Primary,FK);

Regards,

Antonio

Re: Not Exists

tresB — Fri, 30 Jun 2017 06:42:31 GMT

May be like:

So in your case it would be like:

Load * <> Where Not Exists( FK_ID,Primary_ID); //Corrected the order

Re: Not Exists

rahulsingh12 — Fri, 30 Jun 2017 06:47:38 GMT

this is not loading any data for me.

the record count is 0.

Re: Not Exists

antoniotiman — Fri, 30 Jun 2017 06:51:12 GMT

You don't need 2nd LOAD Resident

Try

LOAD * from Table Where Not Exists(Primary,PK);

Re: Not Exists

rahulsingh12 — Fri, 30 Jun 2017 06:52:03 GMT

I just loaded it once

Regards,

Re: Not Exists

tresB — Fri, 30 Jun 2017 06:58:26 GMT

That actually depends. If you don't use second load, the values in first field that appears later in second field would be loaded and I assume OP doesn't want that.

Re: Not Exists

antoniotiman — Fri, 30 Jun 2017 07:04:25 GMT

Yes You are right, 'That actually depends'.

If all values of PK are in Primary, then 2nd LOAD gets 0 Rows.

Re: Not Exists

Muthu — Fri, 30 Jun 2017 07:09:53 GMT

Hi Rahul,

Tresesco is right, You can use that script to load the Primary key which are not FK_ID.

Regards,

Muthu

Re: Not Exists

Peter_Cammaert — Fri, 30 Jun 2017 07:27:19 GMT

Hi Tres.

Just a remark. Isn't it the other way around? The OP says "The Primary may contain FK id but we want only the one's which are not in FK.".

Shouldn't the second LOAD rather be something like this?

t2:

NOCONCATENATE

LOAD * RESIDENT t1 WHERE Not Exists(FKID, PK);

Re: Not Exists

tresB — Fri, 30 Jun 2017 07:32:10 GMT

Yes you are right. I have to read more carefully. Corrected the order.

Re: Not Exists

effinty2112 — Fri, 30 Jun 2017 13:49:55 GMT

Hi rahul,

Something like:

Temp:

Load FK_ID as NoPrimaryLikeThisID From SourceTable;

Load * From SourceTable Where NotExists(NoPrimaryLikeThisID, Primary_ID);

Drop table Temp;

Cheers

Andrew