topic Intersection among 2 different excels in QlikView

Intersection among 2 different excels

Pulkit_Thukral — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Intersection among 2 different excels

Gysbert_Wassenaar — Wed, 13 Apr 2016 06:41:13 GMT

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;

Re: Intersection among 2 different excels

Kushal_Chawda — Wed, 13 Apr 2016 06:57:52 GMT

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