Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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