Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MrSad
Contributor
Contributor

Pivot table- Display only those rows which has value in at least 2 columns

Hi,

Currently, my pivot table is somewhat below in Qlikview

CustomerName_USCity_USName_UKCity_UKName_SECity_SEName_FICity_FI
X20001PacketNY      
X20002PacketNYBottleLN    
X20003  BottleLN    
X20004    CapGT  
X20005PacketNY    NeedleHL

 

I only need rows where there is output in at least 2 groups of data. I need output only this.

CustomerName_USCity_USName_UKCity_UKName_SECity_SEName_FICity_FI
X20002PacketNYBottleLN    
X20005PacketNY    NeedleHL

 

Please help.

 

Labels (1)
5 Replies
sergio0592
Specialist III
Specialist III

Hello,

i made a quick sample with 2 rows. It keeps rows with more than two values. Hope it helps you.

TABLE1:
LOAD *,
RangeSum(if(len(Name_US)>1,1,0), if(len(City_US)>1,1,0),if(len(City_UK)>1,1,0))   as total_count

INLINE
[Customer,	Name_US, City_US, City_UK
X20001,	Packet,	NY,	 	
X20002, Pac, LA, London];

NoConcatenate

TABLE2:
load *
resident TABLE1
where total_count>2;

drop table TABLE1;
Brett_Bleess
Former Employee
Former Employee

Murtuza, did Sergio's post get you what you needed?  If so, do not forget to come back to the thread and use the Accept as Solution button on his post to give him credit for the help as well as confirm to others his solution worked.  If you are still working on things, leave an update.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
MrSad
Contributor
Contributor
Author

Hi Sergio,

In post I have given only example. In reality I have 500k rows of data and around 100 columns so how to use your solution as I can see u have passed the data in the INLINE function.

For now I have managed by creating pivot chart with Vendor as dimension and then once I get all the data in output I just exported it in excel and deleted the unwanted data.

Let me know your views on it. Thanks for your help.

Best Regards

MrSad

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Do you have a sample app?

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Do you mean you have 100+ columns(Name_US,City_US,Name_UK,City_UK etc) in your Data model table?

MC.PNG