topic Re: flag for 1st day pf month based on data in QlikView

flag for 1st day pf month based on data

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: flag for 1st day pf month based on data

tresB — Thu, 23 Feb 2017 12:55:51 GMT

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;

Re: flag for 1st day pf month based on data

sunny_talwar — Thu, 23 Feb 2017 12:56:04 GMT

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

Re: flag for 1st day pf month based on data

tresB — Thu, 23 Feb 2017 13:02:27 GMT

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

Re: flag for 1st day pf month based on data

rahulpawarb — Thu, 23 Feb 2017 13:07:56 GMT

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

Re: flag for 1st day pf month based on data

sunny_talwar — Thu, 23 Feb 2017 13:13:31 GMT

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

Re: flag for 1st day pf month based on data

Thu, 23 Feb 2017 13:14:15 GMT

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..?

Re: flag for 1st day pf month based on data

MK9885 — Thu, 23 Feb 2017 14:44:58 GMT

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.

Re: flag for 1st day pf month based on data

sasiparupudi1 — Fri, 24 Feb 2017 06:16:17 GMT

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