Data Factory aggregate function array within array not allowed

Question

Is it possible to do something like this in Data Factory (data flow expression builder)? I am trying to create an array within an array but with a filter so it doesn't create an array with an empty item.

When I do this, it works however produces an array that looks like fooBar: [{ }] note the array contains a single item.

collect(@(
optionNumber=OptionNo,
price=OptionPrice,
salePricePeriods=array(@(
salePrice=OptionSalePrice,
priceActiveFrom=toString(OptionSaleFrom),
priceActiveTo=toString(OptionSaleTo)))))

Ideally, I want to filter this data by using an expression:

collect(@(
    optionNumber=OptionNo,
    price=OptionPrice,
    salePricePeriods=
    filter(
       collect(@(
          salePrice=OptionSalePrice, 
          priceActiveFrom=toString(OptionSaleFrom),
          priceActiveTo=toString(OptionSaleTo))),
       and(not(isNull(#item.salePrice)), and(not(isNull(#item.priceActiveFrom)), not(isNull(#item.priceActiveTo)))))))

When I do the above I get an error stating

Job failed due to reason: It is not allowed to use an aggregate function in the argument of another aggregate function. Please use the inner aggregate function in a sub-query.;; Aggregate [958abb16-5236-430c-9af6-497495d60469#23243], [958abb16-5236-430c-9af6-497495d60469#23243, first(DataSet#22183, false) AS DataSet#23295, first(Realm#22184, false) AS Realm#23297, first(Territory#22185, false) AS Territory#23299, first(ItemNo#22186, false) AS ItemNo#23301, first(PriceGroupCode#22187, false) AS PriceGroupCode#23303, first(MinOptionPrice#22196, false) AS MinOptionPrice#23305, first(MaxOptionPrice#22197, false) AS MaxOptionPrice#23307, min(MinOptionSalePriceForPeriod#22198) AS MinOptionSalePrice#23309, max(MaxOptionSalePriceForPeriod#22199) AS MaxOptionSalePrice#23311, first(OldestDatePointMinPrice#22203, false) AS OldestDatePointMinPrice#23313, first(OldestDatePointMaxPrice#22204, false) AS OldestDatePointMaxPrice#23315, collect_list(named_struct(optionNumber, OptionNo#22189, price, OptionPrice#22191, salePrice

In which activity are you trying to use this? Or is this the dataflows? — Saideep Arikontham
– Saideep Arikontham, Commented Jan 11, 2023 at 14:22
Can you also provide source data and what you are trying to achieve? — Saideep Arikontham
– Saideep Arikontham, Commented Jan 12, 2023 at 10:51

Dr Schizo · Accepted Answer · 2023-01-12 15:18:02Z

1

Figured it out, annoyingly the filter should have been on the incoming field so like so. The filter expression seems to get evaluated first before adding it to the array.

collect(@(
   optionNumber=OptionNo,
   price=OptionPrice,
   salePricePeriods=
filter(
      array(@(
        salePrice=OptionSalePrice, 
        priceActiveFrom=toString(OptionSaleFrom),
        priceActiveTo=toString(OptionSaleTo))),
      not(isNull(toString(OptionSaleFrom))))))

answered Jan 12, 2023 at 15:18

Dr Schizo

4,4787 gold badges47 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Data Factory aggregate function array within array not allowed

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related