Derived month cloumn based on other column

[dia2021]

Hi All,

I'm trying to create a month-year column based on the number in the other column increases.

For example, I have a version number column in the dataset, and the client explained that as the version number increases each month, it depicts the next month. I need to create a monthly column reading the version number. I'm not sure how to script it in the data load editor. How can I achieve this at the script level? Could someone help me to crack this scenario? 

I am attaching a file for your reference. Thank you!

[stevejoyce]

You'll just need a starting month.  In this case i'm assuming start month is the first row in your soure_data.  but you can derive this differently as needed.  Then each record, addmonths(<start>, rowno())

source_data:
LOAD [version number],
[Month-Year]
FROM
[C:\Users\sjoyce\Desktop\Example - Data.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

let vStartMonth = peek('Month-Year', 0, 'source_data');

derive_month:
load distinct
[version number]
,date(addmonths('$(vStartMonth)', rowno()-1), 'YY-MMM') as newMonthYear
resident source_data
order by [version number] asc;

drop table source_data;

exit script;

[dia2021]

Thanks, @stevejoyce , for the quick reply! I'm very close to deriving the month column with your help.

Apologies for not typing the cleared question earlier. Let me clarify it further; I don't have a month column at all in the dataset. I need to create it based on the version number. 

I know that the version number starting from 4887 based on the attached spreadsheet is July-21.

The question is, how can I manipulate the above script without a month column at all.

Much appreciated!

[stevejoyce · Accepted solution]

Like I said in my first post, you don't need a month column in your original data, but you need a 1st month to start incrementing by.  If you know first version is 21-Jul then you can hard code that into your start variable

let vStartMonth = ''7/21/2021';

source_data:
LOAD [version number]
FROM
[C:\Users\sjoyce\Desktop\Example - Data.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

derive_month:
load distinct
[version number]
,date(addmonths('$(vStartMonth)', rowno()-1), 'YY-MMM') as newMonthYear
resident source_data
order by [version number] asc;

drop table source_data;

exit script;

[dia2021]

@stevejoyce Thanks for the explanation! It is helpful, and I have manipulated the above code according to my need for other datasets. It is working as expected. Thank you again 🙂