flag for 1st day pf month based on data

[#1]

Hello Guys,

i have data for e.g. dates:

01.01.2017

02.01.2017

..

31.01.2017

03.02.2017 (missing 01 and 02.02)

Now I need to build in Calendar flag for month that will put '1' next to first available date of month, which is not always day 01.

Could you please suggest solution?

Thanks,

Tommy

[tresesco]

May be like:

Cal:

Load

          Date,

          Floor(Date) as Key,

          Month(Date) as Month

From <>;

Join

Load

          Min(Key) as Key,

          Month,

          '1' as Flag

Resident Cal Group By Month;

Drop field Key;

[sunny_talwar]

May be using Peek/Previous

MasterCalendar:

LOAD *,

          If(Month <> Previous(Month), 1, 0) as Flag;

LOAD Date(TempDate) as Date,

          Month(TempDate) as Month,

          ...

Resident ....

Order By TempDate;

UPDATE: Modified the script based on tresesco‌'s suggestion

[tresesco]

Nice one without needing a join. Only thing is, for Order By to work, it has to be a Resident load rather than From ...

[rahulpawarb]

Hello Tomasz,

Trust that you are doing great!

Please refer below given draft script:

Data:
LOAD * INLINE [
Dates
01/02/2017
01/03/2017
01/04/2017
02/07/2017
02/08/2017
02/09/2017
03/07/2017
03/04/2017
03/02/2017
04/03/2017
04/07/2017
04/09/2017
];

MapStartDate:
Mapping LOAD
Dates, 1 AS Flag;
LOAD
Month(Dates) AS Month,
Min(Num(Dates)) AS Dates
Resident Data
Group By Month(Dates);

FinalData:
LOAD *, ApplyMap('MapStartDate', Dates, 0) AS Flag
Resident Data;


DROP TABLE Data;

Regards!

Rahul

[sunny_talwar]

Hahahaha yes, I missed that... but I assumed this to be a master calendar script which is most commonly a resident load

[#7]

but I need to have flag for year, quarter, month and week - so I thought if there is a solution to make it smarter than four joins..?

[MK9885]

I'm not sure if you looking for this but I created a Master Calendar script to accommodate almost all Flags.

Check the below link.. use it in your script, check the Date format, check from which year you want the dates to load..

Master Calendar Script

And if you looking for 01 day for each month then you can use (this will give only first day of each month only) Not fully tested in expression but flag works fine.

add this in the above script provided

MonthStart(date(TempDate,'DD/MM/YYYY')) as FirstDate,

And use a Preceding load to get a flag

if(InYear(FirstDate, today(),0), 1, 0) as DateFlag,

The flag will be created for current year only as in Flag 1 will be for 2017 and 0 will be for previous years.

And if you use  if(InYear(FirstDate, today(),-1), 1, 0) as DateFlag, then it will neglect previous year only (2016) and select current year and anything except 2016.

[sasiparupudi1]

Try the following script

let vDateMin = makedate(2015,1,1);

let vDateMax =  makedate(2018,1,1);

MasterCal:

Load

Temp_Date,

MonthStart(Temp_Date) as MS,

Month(Temp_Date) as MMM,

Year(Temp_Date) as YYYY,

WeekDay(Temp_Date) as WeekDay,

If(Match(WeekDay(MonthStart(Temp_Date)),'Mon','Tue','Wed','Thu','Fri') and (Temp_Date=MonthStart(Temp_Date)),1,

    If(Match(WeekDay(MonthStart(Temp_Date))='Sat','Sun') and (Temp_Date=MonthStart(Temp_Date)),0,

     If(WeekDay(MonthStart(Temp_Date))='Sat' and (Temp_Date=MonthStart(Temp_Date)+2),1,

       If(WeekDay(MonthStart(Temp_Date))='Sun' And (Temp_Date=MonthStart(Temp_Date)+1),1,0)

     )))   as Flag

//If(Temp_Date=MonthStart(Temp_Date) and Match(WeekDay(Temp_Date),'Mon','Tue','Wed','Thu','Fri')

//,1,0) as Flag

;

LOAD

date(date#('$(vDateMin)')-1 + recno())  as Temp_Date

AUTOGENERATE (date#('$(vDateMax)')-date#('$(vDateMin)'))+1;

hth

Sas