How to generate input file statistics using Talend 7.3

[mgoel]

I have an input file

col1, col2, col3,col4

1,0,3,

3,1,0,

4,2,0,

I need to prepare output in the below format

field, count_unique_values, count_nulls,population_percentage

col1,3,0,100%

col2,3,0,100%

col3,2,0,100%

col4,0,3,0%

Please advise how to do it?

[Anonymous]

The best way is using the component tAggregateRow, With this component you can create measures you have requested.

[rphilips7161]

if the table is in a database, this may also work for you.

select field,

count( distinct case when results = 'null' then null else to_number( results ) end  ) count_unique_values,

sum(  case when results = 'null' then 1 else 0 end ) count_nulls,

case

when sum( sum(  case when results = 'null' then 0 else 1 end ) ) over ( partition by field) = 0

then 0

else sum(  case when results = 'null' then 0 else 1 end )

/ sum( sum(  case when results = 'null' then 0 else 1 end ) ) over ( partition by field)

end  population_percentage

from (

select case when col1 is null then 'null' else '' || col1 end col1,   /* convert to text to include nulls */

case when col2 is null then 'null' else '' || col2 end col2,

case when col3 is null then 'null' else '' || col3 end col3,

case when col4 is null then 'null' else '' || col4 end col4

from test_unpivot

) unpivot

(   results for field in ("COL1","COL2","COL3","COL4")

)

group by field

[mgoel]

nopes, it is not in database. input is a file.