Creating parameters in Data Factory

Amal Perera 1 Reputation point
2021-02-20T11:19:47.567+00:00

Hi,

I am trying to create some parameters in the data factory. The reason being that the data in the lake is stored in directories with the following structure

YYYY\QQ\MM\DD

I need to parameterise this so that I can refer to the newest created directory.

I have created the year as

Edit: **** The Year calculation does not work ****
formatDateTime(addmonths(convertFromUtc(utcnow(),'India Standard Time'),-3),'yyyy')
(our financial year starts in April and till march 31st next year it is the same year number and I have not tested if this works).

I am ok creating the month and date.

What I need is a way to create the quarter in the format of Q1...Q4.

Is there a quarter function (I could not find any documentation) in DF or is there another way?

Amal

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,898 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Pritam Pathak 1 Reputation point
    2021-02-20T12:50:11.22+00:00

    I can see 2 options of doing this.

    1. Have a lookup activity to do this. The query could be something like this:
      `
      DECLARE @apl DATETIME;

    SET @apl = getutcdate();

    SELECT format(@apl , 'yyyy/Q') + CAST(DATEPART(QUARTER, @apl ) AS VARCHAR(10)) + format(@apl , '/MM/dd') AS FolderName;
    Ouput: 2021/Q1/02/20`

    1. Use dynamic content to define the quarter logic in a variable and then construct your string using this.
      Set Quarter Variable Logic:
      @if(and( greaterOrEquals(int(formatDateTime(utcnow(), 'MM')), 1), lessOrEquals(int(formatDateTime(utcnow(), 'MM')), 3)), 'Q1', <Replicate the condition for rest of the quarters>)
    0 comments No comments