Pivot Table Data Restriction

[Anonymous]

Hi Team,

I need to restrict the data in the table to show only the Airlines having more than one between the Source and Destination.

I have posted my sample source data and required data.

Any ideas on achieving this?

Thanks in Advance.Please let me know if you need more details.

Thanks

Source Data:

Origin	Destination	Airline
Bangalore	Bhopal	JET AIRWAYS
	Calicut	SPICEJET
		INDIGO AIR
	Chennai	SpiceJet
		Air India
	Columbus	ETIHAD AIRWAYS

Required Data:

Origin	Destination	Airline
Bangalore	Calicut	Spicejet
		Indigo Air
	Chennai	SpiceJet
		Air India

[Gysbert_Wassenaar · Accepted solution]

Try a calculated dimension as a replacement for Destination: =If(aggr(Count(Distinct Airline),Destination)>1,Destination)

Or change your chart expressions. For example sum(Amount) would become sum({<Destination={"=count(distinct Airline)>1"}>}Amount)

[asgardd2]

Hello!

I think you need to use aggr() function in dimension. (and turn on option "Suppress When value is Null")

IF(Aggr(Count(Distinct Airline), Destination,Origin) > 1, Destination)

[qlikviewforum]

Both the above solution doesn't seems to be working for me. Only the below seems to be working for me.

IF(Aggr(Count(Distinct Airline), Destination,Origin) > 1, Destination)