Need a help to map the weekends date to Month

[#1]

Can anyone help to map these weekends date to month ?

Month	Weekend
Jan	04-01-2015
	11-01-2015
	18-01-2015
	25-01-2015
	01-02-2015
Feb	08-02-2015
	15-02-2015
	22-02-2015
	01-03-2015
Mar	08-03-2015
	15-03-2015
	22-03-2015
	29-03-2015
Apr	05-04-2015
	12-04-2015
	19-04-2015
	26-04-2015
	03-05-2015
May	10-05-2015
	17-05-2015
	24-05-2015
	31-05-2015
Jun	07-06-2015
	14-06-2015
	21-06-2015
	28-06-2015
Jul	05-07-2015
	12-07-2015
	19-07-2015
	26-07-2015
	02-08-2015
Aug	09-08-2015
	16-08-2015
	23-08-2015
	30-08-2015
Sep	06-09-2015
	13-09-2015
	20-09-2015
	27-09-2015
	04-10-2015
Oct	11-10-2015
	18-10-2015
	25-10-2015
	01-11-2015
Nov	08-11-2015
	15-11-2015
	22-11-2015
	29-11-2015
Dec	06-12-2015
	13-12-2015
	20-12-2015
	27-12-2015
	03-01-2016

[swuehl]

Maybe like this:

LOAD Weekend, if(day(Weekend)>3,month(Weekend),month(AddMonths(Weekend,-1))) as Month;

LOAD Distinct Weekend(makedate(2015)+recno()-1) as Weekend

AutoGenerate 365;

[jonathandienst]

Or perhaps this:

     Month(WeekStart(date) - 1) As Month,

[#4]

Woww thx a lot swuehi, but i m facing prob for  4-10-2015 , the weekend  should be in sep ?

it would be gr8 if could help me to resolve

Thank you

[swuehl · Accepted solution]

Sorry, I missed that. Looking at your sample, I assumed there is some logic like

'if less than 1/2 week (3 days or less) have passed until weekend for a new month, count these days to the preceding month, else to the current month'

Looks my assumption was wrong.

Is there any other logic behind your week assignment?

If not, but you have a lookup table written down somewhere, you can use a mapping in QV:

WeekMap:

MAPPING LOAD * INLINE [

04.01.2015, Jan

11.01.2015, Jan

18.01.2015, Jan

25.01.2015, Jan

01.02.2015, Jan

08.02.2015, Feb

...

];

(Continue with all your weekend dates and assigned month)

Then you can map your weekend dates in your data model:

LOAD

     WeekendDate,

    applymap('WeekMap',WeekendDate) as WeekendMonth,

     ...

FROM ...;

[#6]

Hey it helped a lot..  thx you