11-15-2018 06:26 AM
Dear Community
I need your help with understanding the group by in the attached simple pipeline.
I am supplying the Json data and sorting and grouping it by ID as in the picture but for each Group, i.e. ID. Then I need to first determine if a type of address exists e.g. “BUS” business or “RES” residential and then transform, e.g. via a mapper, to output both the business and residential addresses to Mapper variables $Business and $Residential output as one document/record
Thank you very much for your help in advance,
Andre M
09-25-2019 12:13 PM
Hi tstack,
In the group if I have a date field and I wanted to get the record with the latest date returned, how would I do that?
match $group {
[…, { EndDate: “2019-8-31”, Value }, …] => Value
}
Many thanks
09-26-2019 08:50 AM
Can you clarify a couple of things:
$group
already sorted by date?09-26-2019 08:59 AM
Hi tstack
Thank you for the reply. I need to get a solution for this. The date fields are strings and they are sorted descending before i did the group by via JDBC execute. A sample of the json just after the group by is shown below. I am grouping by “POL_ID” then “CVG_NUM” but I need to return ONLY the first record in each group i.e. with the latest date. How can I do this please??
[
{“groupBy”:{“CVG_NUM”:“01”,“POL_ID”:“DT90000051”},“group”:[{“CVG_NUM”:“01”,“POL_ID”:“DT90000051”,“CO_ID”:“01”,“FIA_EFF_DT”:“2010-04-30”}]},{“groupBy”:{“CVG_NUM”:“02”,“POL_ID”:“DT90000051”},“group”:[{“CVG_NUM”:“02”,“POL_ID”:“DT90000051”,“CO_ID”:“01”,“FIA_EFF_DT”:“2010-04-30”}]},{“groupBy”:{“CVG_NUM”:“01”,“POL_ID”:“DT90000051”}]}]
09-26-2019 09:19 AM
If you just need the first element of the group, you can index it directly, like so:
$group[0]
If you’re wanting to deconstruct it with a match
expression, you can match the first element by making a pattern like the following:
match $group {
[{ CO_ID, FIA_EFF_DT }, …] => CO_ID + " " + FIA_EFF_DT
}
When writing a pattern for match
, write something that looks like the thing you’re trying to match and use ...
as a wildcard. So, the above pattern will match an array with at least one object element that has the given properties.