Skip to main content
  • EN

I can group and count attribute values from a table using sqlcreator

select a.CaseFullRef, count(a.casefullref) as count2

from bcapr as a

inner join bcinsac as b on a.SystemKey = b.systemkey

group by a.CaseFullRef

order by a.casefullref

This gives me the required output

But I need to compare this table with a csv file

I want to group and count the values in the csv file in the same way as the table . How do I group and count the attribute values in the csvfile ?

 

I tried using counter but it just adds a count to each incident of the value .how do I add group by to the counter?

If you want to count how many occurrences of each CaseFullRef you want to use an Aggregator or a Statistics Calculator

e.g.

0684Q00000ArKujQAF.png

 

The inline querier is another option if you're familiar with writing sql

select CaseFullRef, count(1) as count from CSV_Input
group by CaseFullRef

If you want to count how many occurrences of each CaseFullRef you want to use an Aggregator or a Statistics Calculator

e.g.

0684Q00000ArKujQAF.png

 

The inline querier is another option if you're familiar with writing sql

select CaseFullRef, count(1) as count from CSV_Input
group by CaseFullRef

Thank you . The aggregator does the job. 

I am very familiar with SQL. Most of my Translations use SQLCreator . So I would like to get to understand the inline querier However my first attempt with this  and I am completely baffled by the  input and out put ports on this transformer. 

Please could you  send me screen shots of how I would configure the  ports on the inline querier?

Thank you . The aggregator does the job.

I am very familiar with SQL. Most of my Translations use SQLCreator . So I would like to get to understand the inline querier However my first attempt with this and I am completely baffled by the input and out put ports on this transformer.

Please could you send me screen shots of how I would configure the ports on the inline querier?

If you just want to perform an sql statement against one table as in the count example, you just simply link up your csv to the input port, this will populate the input with the table name and the column names and types.

You then create an output port with whatever name is sensible, and enter your sql query

Brilliant Thanks . This is going to save me a lot of time. I was importing csv's into the database , with all the issues of data types etc and querying in Studio manager.

Now I have better options I can use in FME.

Sorry to be so thick but I rarely understand the help on the transformers and so struggle to know how to use them. I very much work on the basis of copy and tweak someone else' s workbench examples.

 

Thanks again

Lizzy

Reply


Community Stats

30,159
Posts
114,479
Replies
37,802
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.