topic Filter records in QlikView

Filter records

renjithpl — Tue, 05 Apr 2016 07:41:39 GMT

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

Re: Filter records

MK_QSL — Tue, 05 Apr 2016 07:42:56 GMT

What is the output for your example you are expecting?

Re: Filter records

renjithpl — Tue, 05 Apr 2016 07:47:03 GMT

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

Re: Filter records

MK_QSL — Tue, 05 Apr 2016 07:53:18 GMT

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;

Re: Filter records

MK_QSL — Tue, 05 Apr 2016 08:05:34 GMT



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;