Exclude Rows from Load based on other Table Values

[#1]

Hi,

I hope that someone can help me with this.

I have an extensive load script which centres on TicketId. Amongst other tables, the 'TicketId' is linked to all 'InvolvedPartyId' and accompanying 'InvolvedPartyTypeId' associated with that ticket. So 1 'TicketId' could have between 1 and 5 'InvolvedPartyId's associated with it and then these involved parties' nature are also determined. The long and the short of it results in us being able to analyse how many individual cases are captured and how many parties, and their relationship, are involved.

The issue that I have encounted is that there are some instances where I need to exclude data based on the InvolvedPartyTypeId. A 'TicketId' could have 2 'InvolvedPartyId's associated with it but if these 2 'InvolvedPartyId's have the 'InvolvedPartyTypeId' of '14' and '7' respectively then I only want to include the data of the 'InvolvedPartyId' where the 'InvolvedPartyTypeId' is '14'. The desired result would be that the 1 'TicketId' would return information for 1 'InvolvedPartyId' with an 'InvolvedPartyTypeId' of '14'.

I believe that this needs to be excluded in the load script as 'InvolvedPartyTypeId's '14' and '7' are still very much relevent, and are used in my application's Set Analysis, it is only when they occur in relationship to the one 'TicketId' that the data relating to '7' needs to be excluded, including the 'InvolvedPartyId' all other data.

Apologies for putting such a specific rather than conceptual illustration of my issue but I wanted to be sure I was conveying my proper meaning. If anyone can help me with the scripting needed I would be really grateful.

Thanks,

Rachel

[#2]

Does anyone have any ideas on this one? Please let me know.

Thanks,

Rachel

[vgutkovsky · Accepted solution]

Rachel,

There may be other ways to do this, but my first thought is this:

data_temp:
LOAD
 TicketId,
 InvolvedPartyId,
 InvolvedPartyTypeId,
 TicketId & '|' & InvolvedPartyTypeId as temp_key,
 if(InvolvedPartyTypeId=7 or InvolvedPartyTypeId=14,1,0) as 4_17_flag
FROM ...;
LEFT JOIN (data_temp) LOAD
 temp_key,
 if(TicketId=previous(TicketId),
 if(InvolvedPartyTypeId=7,
 1,
 0
 ),
 0
 ) as drop_flag
RESIDENT data_temp
WHERE 4_17_flag=1
ORDER BY TicketId,InvolvedPartyTypeId;
data_final:
LOAD
 TicketId,
 InvolvedPartyId,
 InvolvedPartyTypeId
RESIDENT data_temp
WHERE drop_flag=0;
DROP TABLE data_temp;

Cheers,

[#4]

Thank you so much I have spent a while adapting this as the InvolvedPartyTypeIds were not next to each other in sequence but with a bit of a change around this was just what I needed - thanks very much!

Rachel