Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

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

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

Tags (1)
1 Solution

Accepted Solutions

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

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.

View solution in original post

2 Replies

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

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.

View solution in original post

Not applicable

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

Thanks Miguel, worked perfectly.