Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Exclude Rows from Load based on other Table Values

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

1 Solution

Accepted Solutions
vgutkovsky
Honored Contributor II

Exclude Rows from Load based on other Table Values

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,

3 Replies
Not applicable

Exclude Rows from Load based on other Table Values

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

Thanks,

Rachel

vgutkovsky
Honored Contributor II

Exclude Rows from Load based on other Table Values

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,

Not applicable

Exclude Rows from Load based on other Table Values

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