Solved

Remove empty fields

arthy
Rank
Badge +1

Is there a way to extract only the non-empty fields of an oracle table?

Let's say I have a table like this one,

    col_a    col_b    col_c    col_d   col_e
    NULL     NULL     NULL     NULL    5
    NULL     NULL     NULL     8       6
    0        NULL     NULL     NULL    3
    NULL     NULL     NULL     0       1

and the subtable that I would like to extract should be

    col_a    col_d   col_e
    NULL     NULL    5
    NULL     8       6
    0        NULL    3
    NULL     0       1

Thanks

icon

Best answer by xiaomengatsafe 31 January 2017, 22:46

View original

10 replies

erik_jan
Rank
Userlevel 2
Badge +12

After reading the data the NullAttributeMapper transformer (If Null set to Missing) can remove all Null attributes.

itay
Rank
Badge +16

Have you tried a SQL statement on the database to read non-null columns?

select column_name
from user_tab_columns
where table_name='Table_name' and num_nulls=0;
arthy
Rank
Badge +1

After reading the data the NullAttributeMapper transformer (If Null set to Missing) can remove all Null attributes.

@erik_jan, In the example above, the input table has 5 columns while the output table has 3 columns.

 

The NullAttributeMapper won't give this result.

 

erik_jan
Rank
Userlevel 2
Badge +12
@erik_jan, In the example above, the input table has 5 columns while the output table has 3 columns.

 

The NullAttributeMapper won't give this result.

 

I see what you mean:

 

In that case I can only think of using the StatisticsCalculator (max value) and a Tester (If max value > 0). On Failed an AttributeRemover.

 

Not really straight forward but this could work.

 

 

xiaomengatsafe
Rank
Badge +2

Another option is to use the AttributeExploder to turn each attribute name and value pair into individual features, then filter out only ones where value is not empty. And create a schema feature using the filtered result. In a Dynamic Writer, Schema definition can come from Schema features. This will ensure writer don't write out columns without any value.

 

I attached a sample workspace for reference. the source shapefile has one column "COUSUBNS" where all values are empty, on the output, that column is not written.

 

Hope this helps.

 

Also for more details on working with AttributeExploder result, you can reference the beginning part of this tutorial: https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

 

And on Dynamic writing where schema is defined by Schema Feature, you can read more here: https://knowledge.safe.com/articles/1051/index.html

 

remove-columns-with-only-empty-data.fmwt

jdh
Rank
Badge +22

Another option is to use the AttributeExploder to turn each attribute name and value pair into individual features, then filter out only ones where value is not empty. And create a schema feature using the filtered result. In a Dynamic Writer, Schema definition can come from Schema features. This will ensure writer don't write out columns without any value.

 

I attached a sample workspace for reference. the source shapefile has one column "COUSUBNS" where all values are empty, on the output, that column is not written.

 

Hope this helps.

 

Also for more details on working with AttributeExploder result, you can reference the beginning part of this tutorial: https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

 

And on Dynamic writing where schema is defined by Schema Feature, you can read more here: https://knowledge.safe.com/articles/1051/index.html

 

remove-columns-with-only-empty-data.fmwt

I've done the equivalent to this using python to filter the attributes and create the schema feature.

 

 

trevorm
Rank
Badge

Another option is to use the AttributeExploder to turn each attribute name and value pair into individual features, then filter out only ones where value is not empty. And create a schema feature using the filtered result. In a Dynamic Writer, Schema definition can come from Schema features. This will ensure writer don't write out columns without any value.

 

I attached a sample workspace for reference. the source shapefile has one column "COUSUBNS" where all values are empty, on the output, that column is not written.

 

Hope this helps.

 

Also for more details on working with AttributeExploder result, you can reference the beginning part of this tutorial: https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

 

And on Dynamic writing where schema is defined by Schema Feature, you can read more here: https://knowledge.safe.com/articles/1051/index.html

 

remove-columns-with-only-empty-data.fmwt

Hello I'm trying to use the Remove-columns-with-empty-data work space and Cosbuns appears to still show up in my FeatureMerger (End). The solution on my data works perfect on the top pathway and removes all my "all null columns" , but the merger appears to be bringing them all back in.

JennaKAtSafe
Rank
Badge +6

Hello I'm trying to use the Remove-columns-with-empty-data work space and Cosbuns appears to still show up in my FeatureMerger (End). The solution on my data works perfect on the top pathway and removes all my "all null columns" , but the merger appears to be bringing them all back in.

Hi @trevorm​ , would you be able to provide screenshots or a sample of your workspace? I would also suggest starting a new thread with your question as this thread is fairly outdated

trevorm
Rank
Badge

Hello I'm trying to use the Remove-columns-with-empty-data work space and Cosbuns appears to still show up in my FeatureMerger (End). The solution on my data works perfect on the top pathway and removes all my "all null columns" , but the merger appears to be bringing them all back in.

Hi,

I can start a new thread, but how do I reference this threat as I'm trying to reproduce remove-columns-with-only-empty-data.fmwt

If the content on the forums are out of date or no longer working with the current version is there a way to remove them from my search results? I want to remove columns from my .csv that have all "null" values.

 

I have taken this work space and ran it <except the writer>

 

I'll start a new workspace with your help, as this is a great way to ensure my .csv gets cleaned without missing some data at the bottom.

evieatsafe
Rank
Userlevel 1
Badge +15

Hello I'm trying to use the Remove-columns-with-empty-data work space and Cosbuns appears to still show up in my FeatureMerger (End). The solution on my data works perfect on the top pathway and removes all my "all null columns" , but the merger appears to be bringing them all back in.

Hi @trevorm​ to answer one of your questions: we are looking to improve the search function on the community to view the relevant version that applies to you; however, we are not quite there yet. Keep a look out for that in the future!

 

You can also start a new question with links to the resources that are relevant such as this question, articles, and even attach data and workspaces.

 

To answer your question about your column removal workspace question, and I believe that that workspace should work. Unless you have an issue that you could provide an example workspace for, I'm not sure I can fully answer your question. Look forward to a response!

Happy to help, Evie

Reply


Community Stats

28,670
Posts
109,366
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.