Filter records

[renjithpl]

Hi guys,

I have a table like this

Fruits	ID
Apple	1
Mango	1
Orange	1
Apple	2
Mango	2
Orange	3

I have to only show records with Apple and Mango when an id has all three combination (apple, mango and orange) need to eliminate orange.

but when an id has only Orange, I have to retain the orange.

Any idea.

?

Renjith

[MK_QSL]

What is the output for your example you are expecting?

[renjithpl]

Fruits	ID
Apple	1
Mango	1
Apple	2
Mango	2
Orange	3

[MK_QSL]

Temp:
Load * Inline
[
  Fruits, ID
  Mango, 1
  Orange, 1
  Apple, 1
  Apple, 2
  Mango, 2
  Orange, 3
];


Left Join (Temp)
Load ID, IF(WildMatch(AllFruits,'*Apple|Mango|Orange*'),1,0) as Flag;
Load ID, Concat(DISTINCT Fruits, '|') as AllFruits Resident Temp Group By ID;


Final:
Load ID, Fruits Resident Temp
Where (Flag = 0 or Fruits <> 'Orange');


Drop Table Temp;

[MK_QSL · Accepted solution]

Temp:
Load * Inline
[
  Fruits, ID
  Mango, 1
  Orange, 1
  Apple, 1
  Apple, 2
  Mango, 2
  Orange, 3
];




Left Join (Temp)
Load 
  ID, 
  Count(DISTINCT Fruits) as TotalFruits 
Resident Temp
Where Match(Fruits,'Mango','Orange','Apple')
Group By ID;


Final:
Load 
  ID,
  Fruits
Resident Temp
Where (TotalFruits <> 3 or Fruits <> 'Orange');


Drop Table Temp;