Question

Result unexpected by Static Calculator

gogopotter90
Rank
Badge +13

Result unexpected by Static Calculator

 

i have A B C Columns,each column has different years so i want to count how many year duplicate or not in each column and sum the count attribute at the end

so i should do count and sum for each column A B C and get table as result for 3 column in one table beside eachother .

so what i did exactly i used 3 static calculator and each one i defined count and analyze to attribute and output of first a input for second and output of second as input for third but i found the result is incorrect but if i used two static calculator ,the result is erfect but only for 2 columns but i want to do for 3 column .

then i used aggregator to sum the count

Does we have a way to use one static calculator and do sum for 3 columns and count once a time

 

i want to count each the first 3 columns for year and get the sum of each count but i do not want to count empty cell if exist.Thanks

.Thanks

1 Attachment

11 replies

takashi
Rank
Userlevel 2
Badge +17

Could you please post some screenshots that illustrate the worlflow on the canvas and how you did configure each StatisticsCalcuoator parameters? It would be ideal if you could post your workspace and the source dataset, of course.

B
Badge +3

When I get to needing multiple statistics and/or multiple fields then usually I send this off to InlineQuerier instead.

Send your table into InlineQuerier as say "DataTable"

Then get InlineQuerier to return 3 statistical tables:

  • YearACount
  • YearBCount
  • YearCCount 

Using the following 3 separate SQL statements to get the Statistics for each Field in 3 different Output Ports from InlineQuerier

SELECT AttributeA, Count(*) AS CountAttributeA FROM DataTable GROUP BY AttributeA
SELECT AttributeB, Count(*) AS CountAttributeB FROM DataTable GROUP BY AttributeB
SELECT AttributeC, Count(*) AS CountAttributeC FROM DataTable GROUP BY AttributeC

An alternative is to put them all in one statistical lookup table, with a single SQL result called "YearCounts".  To use them downstream is to separate them back out with something like an AttributeFilter to "AttributeName".  The single SQL statement is is like this:

SELECT AttributeA, Count(*) AS YearCount, "AttributeA" AS AttributeName FROM DataTable GROUP BY AttributeA
UNION ALL SELECT AttributeB, Count(*), "AttributeB" FROM DataTable GROUP BY AttributeB
UNION ALL SELECT AttributeC, Count(*), "AttributeC" FROM DataTable GROUP BY AttributeC
gogopotter90
Rank
Badge +13

When I get to needing multiple statistics and/or multiple fields then usually I send this off to InlineQuerier instead.

Send your table into InlineQuerier as say "DataTable"

Then get InlineQuerier to return 3 statistical tables:

  • YearACount
  • YearBCount
  • YearCCount 

Using the following 3 separate SQL statements to get the Statistics for each Field in 3 different Output Ports from InlineQuerier

SELECT AttributeA, Count(*) AS CountAttributeA FROM DataTable GROUP BY AttributeA
SELECT AttributeB, Count(*) AS CountAttributeB FROM DataTable GROUP BY AttributeB
SELECT AttributeC, Count(*) AS CountAttributeC FROM DataTable GROUP BY AttributeC

An alternative is to put them all in one statistical lookup table, with a single SQL result called "YearCounts".  To use them downstream is to separate them back out with something like an AttributeFilter to "AttributeName".  The single SQL statement is is like this:

SELECT AttributeA, Count(*) AS YearCount, "AttributeA" AS AttributeName FROM DataTable GROUP BY AttributeA
UNION ALL SELECT AttributeB, Count(*), "AttributeB" FROM DataTable GROUP BY AttributeB
UNION ALL SELECT AttributeC, Count(*), "AttributeC" FROM DataTable GROUP BY AttributeC

it looks like easy but i tried both of method but i got no output on inspector and also i need to sum the  result of count ,could you provide me with example or explain in screen shots so i can do it in correct way thanks alot

 

 

gogopotter90
Rank
Badge +13

Could you please post some screenshots that illustrate the worlflow on the canvas and how you did configure each StatisticsCalcuoator parameters? It would be ideal if you could post your workspace and the source dataset, of course.

 

i got output that counted year as i want for 3 columns but i want to get sum also so i used aggregator but it gives me output only for one column counted but sum of 2 columns are missed.

year.xlsx

i want to count each the first 3 columns for year and get the sum of each count but i do not want to count empty cell if exist.

the result will be in new table all attributes beside each other.Thanks

 

FME version 2018

takashi
Rank
Userlevel 2
Badge +17

Thanks for sharing the sample dataset. Is this your desired result?

gogopotter90
Rank
Badge +13

Thanks for sharing the sample dataset. Is this your desired result?

it should looks like it but i want also to put construction column and bridge column beside these attributes(teh rest of the table) then i want to get the sum of each count under it ,under the first column result word then under count_A 7 , under Count_B 6,under the count_C 3 ,i want to ask ,is it possible to count number 0 beside missing attribute in each count or we should only remove mising by tester iam asking about 0 bcs i i modified it manuel in the future so i want the sum change later and i need still empty celll .thanks for help

have coomon id so i do not want 1985 to count as 2 bcss each row has his own id as picture

takashi
Rank
Userlevel 2
Badge +17

Sorry, it's hard to understand what table you are attempting to create. Can you show us your desired table which should be created based on the sample data?

gogopotter90
Rank
Badge +13

Sorry, it's hard to understand what table you are attempting to create. Can you show us your desired table which should be created based on the sample data?

i did aggregator and it works good but i have small problem ,how can i count missing value as 0 because it counts it as one .and also when i write in aggregator attribute to sum count a ,count b,count c .it gives me no result for sum . could u give me suggestion how can i write sum for three column once a time and thanks

 

input.xlsx

result_year.xlsx

takashi
Rank
Userlevel 2
Badge +17

Thanks for sharing the sample dataset. Is this your desired result?

In your sample data, there isn't "id" attribute. Where the "id" come from?

Should count_X always be 1 if year_X has a value regardless whether there are duplicate years? (X = A, B, or C)

takashi
Rank
Userlevel 2
Badge +17

I think this workflow would create a table close to your requirement. You can set count_X with conditional value setting - i.e. 1 if year_X has a value, 0 otherwise.

See also this workspace example: count-years.fmwt (FME 2019.2.3)

gogopotter90
Rank
Badge +13

I think this workflow would create a table close to your requirement. You can set count_X with conditional value setting - i.e. 1 if year_X has a value, 0 otherwise.

See also this workspace example: count-years.fmwt (FME 2019.2.3)

It works so supe,thanks alot

Reply


Community Stats

28,688
Posts
109,427
Replies
36,543
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.