Master Calendar (Year issue)

[jobsonkjoseph]

Hi,

I received data in excel format, the Date field ([Bill Date]) is in the format (20170101).

I'm creating a master calendar for getting the missing dates. When i use the particular expression in script editor,

Year([Bill Date]) as Year,

my output is

i'm not able to see any option to convert them into actual years like 2007 or 2017.

Any ideas would be helpful.

Regards,

Jobson

[MarcoWedel]

Hi,

the issue doesn't lie with Year() returning a wrong value that has to be corrected but instead with your initial load of

20170101 as [Bill Date].

Instead of creating a correct date value, i.e. a numerical value of 42736 with a text representation of e.g. 20170101, you just loaded the integer 20170101.

Year(20170101) then returns the Year of the date 11/10/57123 having a numerical value of 20170101.

One solution to load as a proper date should be:

LOAD Date#([Bill Date],'YYYYMMDD') as [Bill Date]
FROM YourExcel

Hope this makes any sense

regards

Marco

[jobsonkjoseph]

Dear Marco,

Thank you for the response.

As mentioned by you, i've modified the script, still to i'm getting the same output.

I'm attaching the screenshot for reference.

Pls do correct me of any mistakes.

[MindaugasBacius · Accepted solution]

A little correction to your script:

Load *

     ,Year(Date#([Bill Date], 'YYYYMMDD')) as Year

     ,Month(Date#([Bill Date], 'YYYYMMDD')) as Month

[jobsonkjoseph]

Thank you Mindaugus, it worked.

[jobsonkjoseph]

Hi,

I've modified the script as mentioned and started working on the application, unfortunately, after scripting the master calendar script, instead of 30 or 31 days per month, i'm getting 100 or 99 days.

Attached is the application with the details. This is a test case scenario and not the actual application.

Any ideas in resolving the same.

The script used for master calendar is taken from Michael Tarallo's post.

Regards

Jobson

[jonathandienst]

May want to start a new discussion because you marked this one as answered.

You have more than 30 days because the day number is messed up. If your source is 20010166, what date does that actually represent? Presumably not day 66 of month 1 of 2001.

[jobsonkjoseph]

Hi Jonathan,

I started a new discussion below is the link

https://community.qlik.com/message/1560304#1560304