Scripting: Dates with No Years (Sometimes)

[raynac]

We have a table that stores special dates (Christmas, Hannukah, Valentine's Day etc).  It has a numerical Month field, a Day field and a Year field, along with a description of what the special date is, which sounds simple enough.

Except that when the development team designed it, they give the user two options:

1. for those dates that change annually (Easter and Hannukah, for example) you enter values in all three fields to denote that in 2018 it falls on one date but in other years the dates can be totally different and you need the year to know exactly.
2. for those dates that never change (Christmas and Valentine's Day, for example) you enter values in the Day and the Month, but for the Year you simply enter a 0 to denote the perennial date.

I am now trying to match up these dates to a table with a single date so that the client can choose, say, Easter and get all Easter Sundays, no matter the year.  That's the easy part because those dates will have three parts.

Can anyone tell me how to join a table with a date with a year of 0, and a data table with date with an actual year, based on just the month and day?

Any help would be most appreciated.

[vishsaggi]

May be somthing like below Where you get year 0, just use like

LOAD *, MakeDate(Year, Month, Day) AS PerennialDate;

LOAD

Month,

Day

Year(Today()) AS Year

......;

[raynac]

Hi Vishwarath!  Thanks so much for your response.

Unfortunately, the view has to account for date from last year and next year and any other years in the database.

So Christmas Day has to load as (essentially multiple rows) for, say, 25-12-2010 and 25-12-2011 etc... right on up to the last date that exists in the database.

I wondered if a master calendar would help, but I still wasn't able to figure out how to get it to "create" the extra rows to match each year.

[peterbergman]

An idea can be to resident load all the years in the app and then join with the rows that has 0 as year. Here is some script that maybee could be useful.

tmpAllYearsInApp:

LOAD DISTINCT tmpYEAR RESIDENT TableWithAllYears;

JOIN (tmpAllYearsInApp)

LOAD MakeDate(tmpYEAR, Month, Day) AS PerennialDate

FROM TableToLoad

WHERE Year = 0;

RENAME TABLE tmpAllYearsInApp to newTableName;

First you will have a tmp-table with all the distinct years that exists in the app. Then you will read all the data with year =0 and with the join you will have multiple rows.

I.e. you have the yeras 2017,2018,2019 in the app. Then you have 25 ,12 , 0  (christmas day). After the join you will have three rows, 25-12-2017 and 25-12-2018 and 25-12-2019.

Regards

Peter

[vishsaggi]

Well, sorry i quite did not follow you. Could you please elaborate a little with some sample data. Probably yes, you have to use Master Calendar for missing dates.  

[peterbergman · Accepted solution]

Try this script. I will also attach a qlikviewfile with the same script.

// First we have all the data about holidays. 0 = Same date every year

// Easter day is different from year to year

Holidays:

LOAD * INLINE [

Day, Month, Year, Description

25 , 12 , 0, Christmas day same date every year

21 , 4 , 2019, Easter day 2019

1 , 4, 2018, Easter day 2018

16 , 4, 2017, Easter day 2017

14 , 2 , 0, Valentines day every year

];

// Load all the distinct years from the Holiday-table exept for year = 0

tmpHolidays:

LOAD DISTINCT Year as tmpYear

Resident Holidays

where Year <> 0;

// JOIN this tmp-table with data from holidays but with the year = 0

// The result will be three rows with christmas day and valentines day

JOIN (tmpHolidays)

LOAD Day, Month , Description

Resident Holidays where Year=0;

////Concatenate rows from the holidays table where year is not zero. Easter day

Concatenate (tmpHolidays)

LOAD Day, Month, Year as tmpYear, Description

Resident Holidays

where Year <> 0;

// Drop the inline table that we started with

Drop table Holidays;

Holidays:

LOAD MakeDate(tmpYear, Month, Day) as PerennialDate, Description, tmpYear as Year, Month, Day

Resident tmpHolidays;

// drop the tmpHolidaystable

drop table tmpHolidays;

[raynac]

Hi Peter!  This looks amazing. Thank you so much for all the work you put in!

Now, I have one question.  The layout of the table in my actual software is exactly the same as the INLINE LOAD you posted:

So...can I just use that instead?  Was there a reason you chose that method, because my clients are worldwide and they will put their own special dates into the database.  So I cannot hard code them.  Or were you just showing me an example of how it could be done?

[raynac]

Scratch my silly question above!!

I went in and substituted my table for your inline load and it worked perfectly.

Again, I cannot thank you enough!