Converting a Case Transform in ADF Mapping DataFlow

sayan nandi 0
Viewed 62

I am currently building a DF in ADF where i am converting the below Query which is already placed in another ETL tool called BigDecission. The Query looks like below

SELECT
    Asset_ID,
    MAX(CASE WHEN meter = 'LTPC' THEN reading_date ELSE NULL END) AS LTPC_Date,
    MAX(CASE WHEN meter = 'LTPC' THEN page_Count ELSE NULL END) AS LTPC,
  
FROM
    mv_latest_asset_read
GROUP BY
    Asset_ID

While converting this piece in ADF DF i have used AGGREGATE transform and done GROUP BY "ASSET_ID" . In the AGGREGATES Tab i am deriving the column "LTPC_DATE" and "LTPC" with below mentioned code.

LTPC_DATE ---- > max(case(METER=='LTPC',READING_DATE)) enter image description here

LTPC ---- > max(case(METER=='LTPC',PAGE_COUNT)) enter image description here

But in the output i am getting null values which shouldn't be the case. Can anyone identify the right way to do it.

azure-data-factory azure
1 Answers

I followed the same approach to reproduced above and getting proper result.

Please check the below:

My source data:

enter image description here

Here I have taken 2 additional columns using derived column transformation and giving a sample value.

enter image description here

Group By and aggregate:

enter image description here

Used max(case(condtion,expression)) here.

enter image description here

Result in Data preview:

enter image description here

Try to check your projection in the source. Also, transform this to a sink file and check if it gives correct result or not.

If it still gives same, you can try maxIf(condition, expression) as suggested by @Mark Kromer MSFT.

enter image description here

The above also giving the same result for me.

If your source is a database, you can try query option in the source of dataflow and give the above query.

enter image description here

After Importing projection, you can see the desired result in the Data preview.

enter image description here

Rakesh Govindula0
Related