Transpose ? - Create Filter

[JDong]

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

[AngelosPachis · Accepted solution]

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

[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

[AngelosPachis]

Hi @JDong 

Nulls are to be expected because there are no values populating those cells.

Now to help you with the second query, I will need something more representative of your dataset, as it's hard to troubleshoot it by speculating what the issue might be; maybe you can attach a more representative sample of the data ?

Cheers,

Angelos

[JDong]

Hi @AngelosPachis 

School	Address	Year	Maths	Science	Physics	Biology	Chemistry
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

This is exactly the data I have , but as marked the Address 1 (both the 1 values are not transposing and shows 0)

The field datatype I have changed to int16 from string but still same result. The data is in excel and as attached from original.

Thanks

[AngelosPachis · Accepted solution]

@JDong it appears to be working when I try it

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?

[JDong]

Yes..sorry overlooked...values were getting filtered in an earlier step