question

AmalPerera-8023 avatar image
0 Votes"
AmalPerera-8023 asked ·

Creating parameters in Data Factory

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
· 3
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AmalPerera-8023,

Following up to see if the below suggestion from @PritamPathak-1763, was helpful? If this answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Thank you

0 Votes 0 ·

Hi,

My apologies for not replying earlier. I have not been able to test this as I have been involved in some other work. I will test this within a couple of days and get back to you. Many thanks for the support.

Amal

0 Votes 0 ·

Sure, thanks for the update! @AmalPerera-8023

0 Votes 0 ·

1 Answer

PritamPathak-1763 avatar image
0 Votes"
PritamPathak-1763 answered ·

I can see 2 options of doing this.

  1. Have a lookup activity to do this. The query could be something like this:
    DECLARE @date DATETIME; SET @date = getutcdate(); SELECT format(@date, 'yyyy/Q') + CAST(DATEPART(QUARTER, @date) AS VARCHAR(10)) + format(@date, '/MM/dd') AS FolderName;
    Ouput:
    2021/Q1/02/20

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



· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.