Trying to Load a month to a field based on another field value

[#1]

Hi,

I have a simple field holding 4 numeric values (1-4) representing "this month", "next month", "3 months" and "Never". I want to load in another field with this record set that is populated with the current month (based on today's date) for the "this month" records, next month for the "next month" records and so on..

For example if loaded now, i'd have one field with September assigned to all "this month" record, October to all "next month records and so on. Also want to do the same for year but I'm sure that would follow the same principle.

Sure this is straightforward, just can't get my head around it!

Thanks
Nick

[Miguel_Angel_Baeyens · Accepted solution]

Hello Nick,

If that information is in the same table, you can do

DateMapTemp:LOAD 1 AS PlainDate, Date(Today(), 'MMMM') AS DateNameAUTOGENERATE 1;LOAD 2 AS PlainDate, 'Never' AS DateNameAUTOGENERATE 1;LOAD 3 AS PlainDate, Date(AddMonths(Today(), 1), 'MMMM') AS DateNameAUTOGENERATE 1;LOAD 4 AS PlainDate, Date(AddMonths(Today(), 3), 'MMMM') AS DateNameAUTOGENERATE 1; DateMap:MAPPING LOAD PlainDate, DateNameRESIDENT DateMapTemp; DROP TABLE DateMapTemp;LOAD *, ApplyMap('DateMap', PlainDate) AS DateNameFROM TABLE.QVD (QVD);

It has not turned out as short as I'd like but I think it will work, and it will be updated every time you reload. I'd recommend you to build a master calendar so you have all time dimensions you want (month name, month number, year, previous year, month-year, etc)

Hope that helps.

[#3]

Thanks Miguel, worked perfectly.