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

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

MrSad — Sat, 16 Nov 2024 02:03:14 GMT

Hi,

Currently, my pivot table is somewhat below in Qlikview

Customer	Name_US	City_US	Name_UK	City_UK	Name_SE	City_SE	Name_FI	City_FI
X20001	Packet	NY
X20002	Packet	NY	Bottle	LN
X20003			Bottle	LN
X20004					Cap	GT
X20005	Packet	NY					Needle	HL

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

Customer	Name_US	City_US	Name_UK	City_UK	Name_SE	City_SE	Name_FI	City_FI
X20002	Packet	NY	Bottle	LN
X20005	Packet	NY					Needle	HL

Please help.

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

sergio0592 — Sun, 03 Nov 2019 17:10:01 GMT

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;

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

Brett_Bleess — Thu, 07 Nov 2019 21:19:44 GMT

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

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

MrSad — Thu, 07 Nov 2019 22:17:21 GMT

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

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

Arthur_Fong — Fri, 08 Nov 2019 03:46:03 GMT

Do you have a sample app?

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

Arthur_Fong — Fri, 08 Nov 2019 04:01:51 GMT

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