topic Re: Excluding Deal IDs from from being loaded in a table using an excel file connection in App Development

Excluding Deal IDs from from being loaded in a table using an excel file connection

elaiken3 — Sat, 16 Nov 2024 05:01:57 GMT

Hello,

I need some help figuring out how to exclude certain rows from being loaded based on their deal ids. I have an excel file with a list of deal ids that should be excluded from being loaded in my main tables. The deal ids that I want excluded from being loaded in TABLE1 are in the Exception Table. Here's how it looks

Exception:
LOAD
DEAL_ID as "DEAL_ID_EXCEPTION"
FROM [lib://DEAL_ID_TEST_Deals]
(ooxml, embedded labels, table is UAT);

TABLE1:

LOAD

deal_id, region, project_name, title;

SQL select cobs_id, region, project_name, title

from deal_db.template_deal

I want to load ONLY the deal ids that are not in the Exception table. Any help would be greatly appreciated

Re: Excluding Deal IDs from from being loaded in a table using an excel file connection

Anil_Babu_Samineni — Thu, 08 Aug 2019 17:29:41 GMT

Perhaps this?

Exception:
LOAD
DEAL_ID as "DEAL_ID_EXCEPTION"
FROM [lib://DEAL_ID_TEST_Deals]
(ooxml, embedded labels, table is UAT);

TABLE1:

LOAD

deal_id, region, project_name, title Where Not Exists (DEAL_ID_EXCEPTION);

SQL select cobs_id, region, project_name, title

from deal_db.template_deal

Re: Excluding Deal IDs from from being loaded in a table using an excel file connection

elaiken3 — Thu, 08 Aug 2019 17:37:18 GMT

It gave me this error

The following error occurred:

Field 'DEAL_ID_EXCEPTION' not found

Re: Excluding Deal IDs from from being loaded in a table using an excel file connection

Anil_Babu_Samineni — Thu, 08 Aug 2019 17:39:42 GMT

My bad, Reverse

TABLE1:

LOAD

deal_id, region, project_name, title;

SQL select cobs_id, region, project_name, title

from deal_db.template_deal;

Exception:
LOAD
DEAL_ID as "DEAL_ID_EXCEPTION"
FROM [lib://DEAL_ID_TEST_Deals]
(ooxml, embedded labels, table is UAT) Where Not Exists (DEAL_ID_EXCEPTION);

More Info : https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/InterRecordFunctions/Exists.htm

Re: Excluding Deal IDs from from being loaded in a table using an excel file connection

elaiken3 — Thu, 08 Aug 2019 17:57:08 GMT

That ran all the way through but didn't give the desired result of excluding the rows in TABLE1 that correspond to the IDs in DEAL_ID_EXCEPTION

Re: Excluding Deal IDs from from being loaded in a table using an excel file connection

Anil_Babu_Samineni — Tue, 13 Aug 2019 12:50:56 GMT

Please provide sample data set.