Search code examples
azure-data-factoryodataazure-synapse

Azure Data Factory: Get the max-value from a table with the help of CreatedAt-column within an variable expression

This time I am using the following manual:
https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/extracting-sap-data-using-odata-part-7-delta-extraction-using/bc-p/4202197#M976
We are talking about this picture: enter image description here


What are we trying to do:
  1. Lookup: Get a table from source
  2. Set variable: Check if the table is empty
  3. If empty, write an empty value to variable
  4. If filled, get the latest value from column DeltaToken with the help of column CreatedAt

My idea (I assumed that the DeltaToken does increase with time):

@if(equals(activity('ll_delta').output.count, 0), '', max(activity('ll_delta').output.value.DeltaToken))

Error:
The expression 'if(equals(activity('ll_delta').output.count, 0), '', max(activity('ll_delta').output.value.DeltaToken))' cannot be evaluated because property 'DeltaToken' cannot be selected. Array elements can only be selected using an integer index.

His idea:
Order the DeltaToken by its CreatedAt-Time and pull the latest --> Would anyone mind helping me to achieve this goal? I would appreciate your help

Edit_1
ll_delta: enter image description here


ODataURL:
@concat(pipeline().parameters.URL, pipeline().parameters.ODataService, '/')

Entity:

@concat('DeltaLinksOf', pipeline().parameters.Entity)

Sample Output by ll_delta:

{
"count": 5,
"value": [
    {
        "DeltaToken": "D20240815145137_000036000",
        "CreatedAt": "2024-08-15T14:51:37Z",
        "IsInitialLoad": true
    },
    {
        "DeltaToken": "D20240815171231_000021000",
        "CreatedAt": "2024-08-15T17:12:31Z",
        "IsInitialLoad": false
    },
    {
        "DeltaToken": "D20240816091538_000017000",
        "CreatedAt": "2024-08-16T09:15:38Z",
        "IsInitialLoad": false
    },
    {
        "DeltaToken": "D20240816091955_000036000",
        "CreatedAt": "2024-08-16T09:19:55Z",
        "IsInitialLoad": false
    },
    {
        "DeltaToken": "D20240816092232_000119000",
        "CreatedAt": "2024-08-16T09:22:32Z",
        "IsInitialLoad": false
    }
],...

Our solution/workaround for now within activity Set variable/v_deltatoken (it does work):

@if(equals(activity('ll_delta').output.count, 0), '', last(activity('ll_delta').output.value).DeltaToken)

Issues:

  1. We are not sure that the last value will always be the most recent one, hence I would like to get the max-value.
    We have tried it by query the linked service via $orderby...desc incl. top1 and select but it seems as if the orderby-clause does not work as desired, hence we always pull the oldest one.
  2. Next best solution could be something with ForEach but it seems to be very complicated, isn't it?
Solution

  • Order the DeltaToken by its CreatedAt-Time and pull the latest

    To achieve your requirement, you need to first get the max CreatedAt and based on it you need to filter the output.

    Here is the sample solution I tried with sample data you provided:

    • First as you did pull the data using lookup activity.
    • You need to get the max CreatedAt using the Foreach activity and set variables.

    Pass the Lookup output to for each activity as below:

    enter image description here

    Under Foreach activity take two set variables of string type and as initial date, max date to get the max CreatedAt date as below:

    enter image description here

    enter image description here

    • Then filter the Lookup data using the max date you got after all iteration of for each activity enter image description here

    After this you will get the latest values.

    OUTPUT:

    enter image description here