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