String interpolation, it's difficult

Question

Hi

let me explain the issue. My data flow has a parameter, process date filled with 2022-03-02.
An incoming source has a reference field CASE_REF. This field looks like:

1. 'FRD.20220112.57'
2. 'FRD.20220203.28'
3. 'FRD.20220301.35'
4. 'FRD.20220112.33' etc.

Position 5 up until 10 are YYYYMM
I have to select the rows where YYYYMM in this field CASE_REF = YYYYMM of the process date - 2 months.
So , if process date = 2022-03-01, it has to pick rows 1 and 4 where YYYYMM = 202201

I first tested this without the subtraction of two months

"SELECT CASE_REF FROM Table where substring(CASE_REF,5,6) = '{concat(substring($p_processdate,1,4),substring($p_processdate,6,2))}'"

That worked. Now I tried to build something with deduction of two months, but I can't get it working.

"SELECT CASE_REF FROM Table where substring(CASE_REF,5,6) = '{year(subMonths(toDate($p_processdate),2)) * 100 + month(subMonths(toDate($p_processdate),2))}'"

First it kept on giving back the result of the first query, later I only got 'DSL stream has parsing errors'. Even after restarting ADF and Debug.

Hope someone can help.
I still find that string interpolation hard to understand.

Regards
Ron

Answer 1

Hi @Poel van der, RE (Ron) ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question.

As I understand your query, the part of the string carries date value and wherever the date matches the provided processed date -2 months , you are trying to retrieve only those records. Please let me know if my understanding is incorrect.

You can make use of Filter transformation in this case as you want to filter out the records based on some conditions. Here are the steps you need to perform:

1. Create the parameter: processdate with string datatype

2. In select transformation, use this query : "select CASE_REF,substring(CASE_REF,charindex('.',CASE_REF)+1, 8) as Updated_CASE_REF from testStringInterpolation" and import projection.
Note: Replace 'testStringInterpolation' with the tableName in your case

3. Use Filter transformation and provide this expression as the filter condition: equals(year(toDate(Updated_CASE_REF,'yyyyMMdd')),year(toDate(replace($processdate,'-',''),'yyyyMMdd'))) && equals(month(toDate(Updated_CASE_REF,'yyyyMMdd')),month(subMonths(toDate(replace($processdate,'-',''),'yyyyMMdd'),2)))

For more details, kindly check out this video: How to fetch dates belonging to previous month using mapping dataflow

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on button and take a satisfaction survey whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators