topic How do I compare two separate columns to exclude items in Load Script and only show exceptions? in QlikView

How do I compare two separate columns to exclude items in Load Script and only show exceptions?

Mon, 08 Jul 2013 18:13:53 GMT

I have two columns in a load script that I want to compare.

One column is dynamic and the other static. I only want to show the records that are not in both columns.

Here is the script:

Directory;
[tmp_exclusions]:LOAD
      Subject,
     Date (StartDate) AS [Start Date],
     Time (StartTime) AS [Start Time],
     Timestamp(Date(StartDate) + Time(StartTime,'h:mm:ss TT')) AS StartDateTime,
     MeetingOrganizer AS Organizer,
     MeetingResources AS [Conference Room],
     Categories AS Trainer,
     Description,
     SUBFIELD(RequiredAttendees,';') AS TraineeFROM
[Mistys Calendar Export.xls]
(biff, embedded labels, table is [Calendar$])
WHERE Subject LIKE 'Training_*';

Directory;LEFT JOIN (tmp_exclusions)LOAD [Trainee Exclusions] as ExclusionsFROM
Exclusions.xlsx
(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE LOAD *RESIDENT tmp_exclusions
WHERE Trainee <> Exclusions;
DROP TABLE tmp_exclusions;

The operative portion is the last "Where" statement. I only want to load records where the values in the "Trainiee" column do not exist in the "Exclusions" column.

Bue the line: WHERE Trainee <> Exclusions; is not filtering out the duplicates as I expected. Now, if I cange the line to:

WHERE Trainee = Exclusions; it does only display those items that appear in both lists, as expected.

So, what is the correct way to state "WHERE (the items in) Trainee doe not equal (the items in) Exclusions?

Thank smuch,

Steve

Re: How do I compare two separate columns to exclude items in Load Script and only show exceptions?

swuehl — Mon, 08 Jul 2013 18:20:13 GMT

LOAD your exclusion column first

LOAD [Trainee Exclusions] as Exclusions

FROM
Exclusions.xlsx
(ooxml, embedded labels, table is Sheet1);

Then your main table:

LOAD * where not exists(Exclusions, Trainee);
LOAD
      Subject,
     Date (StartDate) AS [Start Date],
     Time (StartTime) AS [Start Time],
     Timestamp(Date(StartDate) + Time(StartTime,'h:mm:ss TT')) AS StartDateTime,
     MeetingOrganizer AS Organizer,
     MeetingResources AS [Conference Room],
     Categories AS Trainer,
     Description,
     SUBFIELD(RequiredAttendees,';') AS Trainee

FROM
[Mistys Calendar Export.xls]
(biff, embedded labels, table is [Calendar$])
WHERE Subject LIKE 'Training_*';

Re: How do I compare two separate columns to exclude items in Load Script and only show exceptions?

Mon, 08 Jul 2013 19:18:12 GMT

Perfect!

Worked like a charm.

Thanks a million,

Steve-O