Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

MrSad
New 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.

 

Tags (1)
Labels (1)
5 Replies
sergio0592
Valued Contributor II

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

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;
Support
Support

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

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
MrSad
New Contributor

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

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

Partner
Partner

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

Do you have a sample app?

Partner
Partner

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

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

MC.PNG