Hi, I have 20 columns in my dataset and of this format SchoolAddressYearMathsSciencePhysicsBiologyChemistryGeology1Address 12001 11 2Address 22002 1 3Address 3200311 4Address 420041 1 5Address 52005 1 6Address 62006 1 7Address 72007 1 8Address 82008 1 9Address 92009 1 10Address 102010 1 11Address 112011 1 12Address 1220121 13Address 132013 14Address 142014 15Address 1520151 1 16Address 162016 1 17Address 172017 1 18Address 182018 1 I want to now combine the columns MathsSciencePhysicsBiologyChemistryGeology To a single column so the data can be filtered on multi select. Can we create a subject filter and then select specific values (multiple values) to filter the data. SubjectMathsSciencePhysicsBiologyChemistryGeology Would this be right wa to filter the datasets ? Please advise how this design can be achieved. Thanks

Transpose ? - Create Filter

Hi,

I have 20 columns in my dataset and of this format

School	Address	Year	Maths	Science	Physics	Biology	Chemistry	Geology
1	Address 1	2001		1	1
2	Address 2	2002			1
3	Address 3	2003	1	1
4	Address 4	2004	1				1
5	Address 5	2005					1
6	Address 6	2006			1
7	Address 7	2007			1
8	Address 8	2008			1
9	Address 9	2009			1
10	Address 10	2010				1
11	Address 11	2011					1
12	Address 12	2012	1
13	Address 13	2013
14	Address 14	2014
15	Address 15	2015	1			1
16	Address 16	2016					1
17	Address 17	2017					1
18	Address 18	2018						1

I want to now combine the columns

Maths

Science

Physics

Biology

Chemistry

Geology

To a single column so the data can be filtered on multi select. Can we create a subject filter and then select specific values (multiple values) to filter the data.

Subject

Maths

Science

Physics

Biology

Chemistry

Geology

Would this be right wa to filter the datasets ? Please advise how this design can be achieved.

Thanks

Datasets

Developer Tools

Tips and Tricks

Accepted answers

AngelosPachis

Hey @JDong ,

To bring all subjects in a single column you should use a transpose tool, where you have to select the "Key columns" as those that you don't want to pivot onto a vertical axis.

Then you can use a filter tool with multiple OR statements (i.e. [Subject]="Maths" OR [Subject]="Science" OR ..... )

or an In statement (i.e. [Subject] IN "Maths","Science","Physics")

Whatever falls out of the T output anchor are records that belong to the subjects you specified in the filter tool.

Hope that helps,

Angelos

example.yxmd

AngelosPachis

@JDong it appears to be working when I try it

Screenshot 2021-05-06 092619.jpg

So for address 1, I can still see 1 for Science and Physics. Can you double-check that you have configured the tool in the right way?

example_v2.yxmd

All comments

AngelosPachis

Hey @JDong ,

To bring all subjects in a single column you should use a transpose tool, where you have to select the "Key columns" as those that you don't want to pivot onto a vertical axis.

Then you can use a filter tool with multiple OR statements (i.e. [Subject]="Maths" OR [Subject]="Science" OR ..... )

or an In statement (i.e. [Subject] IN "Maths","Science","Physics")

Whatever falls out of the T output anchor are records that belong to the subjects you specified in the filter tool.

Hope that helps,

Angelos

example.yxmd

JDong

@AngelosPachis

Thanks !

For some reason my Value column output shows lot of Null values and some of the '1' value is not showing up. Any idea if this is because of the excel dataset ? How can I get this working perfectly...

Ideally I should see 1 for 2 rows here

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335