topic Re: How to generate input file statistics using Talend 7.3 in Talend Studio

How to generate input file statistics using Talend 7.3

mgoel — Fri, 15 Nov 2024 23:36:46 GMT

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 — Wed, 20 Oct 2021 14:04:10 GMT

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

rphilips7161 — Thu, 21 Oct 2021 01:20:32 GMT

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 — Thu, 21 Oct 2021 05:06:10 GMT

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