Support Questions
Find answers, ask questions, and share your expertise

How to use a calculated value with QueryRecord?

New Contributor

Hi guys,

I'm trying to understand how to use the QueryRecord value that I have calculated from a flowfile with SQL.

 

I have a flowfile with, for example, 4 flat records and I want to calculate a SUM() of worked hours from each record. I make it work so I now have a summarized value of hours as output in a separate relationship, but how can I add this value to the original flowfile? As a new attribute or other way?

To me the flowfile and the calculated value are split in two separate flowfiles with almost nothing holding them together.

I must be missing something?

Thanks for any help or hints...

//Stefan

 

SQL:

SELECT SUM("hours") FROM FLOWFILE

 

Sample Json:

[ {
"date": "2019-08-30",
"hours": 1
},
{
"date": "2019-09-30",
"hours": 2
},
{
"date": "2019-10-15",
"hours": 3
},
{
"date": "2019-11-01",
"hours": 2
} ]

 

Flowfile data for the new relationship that I want to add to the original flowfile:

[{"EXPR$0":8}]

3 REPLIES 3

Contributor

You can use "ConvertAvroToJSON" and then the "EvaluateJsonPath" Processor and add there the Property like "workingHours" and as Value you set your JsonKey like $.hours

With this you add it to the attribute of your FlowFile.

Explorer

SELECT ID,

ItemName,

NetPrice, [NetPrice] + [NetPrice] / 100 * 19 AS GrossPrice FROM tblItems;

I hope this piece of code works for you!
Regards,

Lewis

New Contributor

Thanks, but I have no problem writing the calculation code. It's the new relationship that gets the summarized value I can't really handle in a good way. It's becoming a new flowfile and I loose the connection to the original flowfile.