How to match a value with the header

I have to put value based on the headers like this :

I have a variable :

  1. “due_date” contains “yyyy-mm-dd”
  2. Period (contains value between 0 until 60 depends on customer ID so it can be vary)

I want to output a amount value based on due_date and the period > 0

So for example :

Due_Date = 2022-07-05
Value = 500

I can convert from yyyy-mm-dd to yyyy-mm so it will be “2022-07”

And my excel header start from may, I want it to be put on header 2022-07 like this :

image

same goes if the due_date start on may, then put 300 on column 2022/05

if the due_date start on June, then put 400 on 2022/06

How to execute it since it’s NOT read excel, but it’s from sql query and I have to write it to data table excel

To convert from yyyy-mm-dd to yyyy-mm you can use CDATE(date).ToString(“yyyy-mm”)
and use if condition to check if the mm increasaed or not if increased then incremenr the variable by 100

Hi @Rhys18 !
You can just give a logic

  1. If Due Date contains 2022/05 then write in column 3(2022/05) Value 300. You Can do it using If activity or you can also use Lambda Query here. :slightly_smiling_face:

Hi,

I did solved the convert date, maybe you can share a workflow for the if conditions?

But I got a values for each months. Meaning there always will be a value when it’s Month-1 (Means last month is may until maturity date (assuming maturity date is 2028) there will be ALWAYS contain a value for each month). I never heard lambda query before, what is that? Also Maybe you can share workflow for this logic? Thanks :slight_smile:

Sure @Rhys18.
Give me some time

DTSelectLambdaQuery.xaml (5.5 KB)

This will first look into 1st column value i.e DEF-456, then it will fetch the corresponding value from 2022/05 column.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.