Intersection among 2 different excels

[Pulkit_Thukral]

Hi ,

Requirement is that i have list of tickets (overall) and then i have a list of excluded tickets (Both in xls files) that is to be deleted from overall tickets by creating a custom flag in load script.

Example - i have 20 tickets in total .

               Among 20, 5 are to be excluded from calculation .

               In load script i want to create a flag (Ex. Exception_Ticket=1) that tells which are those 5 tickets among 20.

How can this be achieved?

[Gysbert_Wassenaar · Accepted solution]

ExcludedTickets:

LOAD

     TicketID as ExcludeID

FROM

     Exceptions.xlsx (ooxml, ...etc)

     ;

Tickets:

LOAD

     TicketID,

     ...other fields...

FROM

     AllTickets.xlsx (ooxml, ...etc)

WHERE

     NOT Exist(ExcludeID, TicketID)

;

DROP TABLE ExcludedTickets;

[Kushal_Chawda]

TotalTicket:

LOAD *

FROM Totalticket.xlsx;

// Join the table on Ticket no or relevant ID between Two Table

left join(TotalTicket)

LOAD *,

         1 as Exception_Ticket1

FROM Excludeticket.xlsx;

Final:

noconcatenate

LOAD *,

         if(isnull(Exception_Ticket1),0,Exception_Ticket1) as Exception_Ticket

Resident TotalTicket;

drop table TotalTicket;

Now you can select the value from Exception_Ticket . 1 means excluded