CODEWITHSUNDEEP
azure-data-factory
Dr Schizo
Dr Schizo

Reputation: 4366

Data Factory aggregate function array within array not allowed

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

Upvotes: 0

Views: 138

Answers (1)

Dr Schizo
Dr Schizo

Reputation: 4366

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))))))

Upvotes: 1

Related Questions