topic Re: year, month ? in QlikView

year, month ?

Sat, 19 Mar 2016 07:07:13 GMT

HI All,

Am having Year(2016) , Month(Jan,Feb,Mar & So on..) in My table. Need to connect my master Calendar, How can i connect this.

Can any provide me the Script..

Regards,

Helen

Re: year, month ?

Kushal_Chawda — Sat, 19 Mar 2016 07:13:17 GMT

Do you have date in your data?

For now create the Field in your data and master calendar as below and link on that field

=date(date#(Year&Month,'YYYYMMM'),'MMM-YYYY') as Link

Re: year, month ?

agni_gold — Sat, 19 Mar 2016 07:48:07 GMT

If you have Year and Month field in your data , then you need to make date range from that in load script,

using YearStart and YearEnd

Re: year, month ?

Anonymous — Sat, 19 Mar 2016 08:38:43 GMT

Hi Helen,

If you have multiple sub date fields in your FACTS table, you better concat them as a single date format to avoid Syntetic keys with your master calendar.

So first concat them in Facts table to have only one date KEY (full date using first day of each month or just yearmonth format if enough for you)

Then have the same date KEY generated in your Mastercalendar which could also have sub dates fields for analysis (Year, Month....):

MasterCalendar:

LOAD

date(date#(Year&Month, 'YYYYMMM'), 'MMM-YYYY') AS MonthYear,

...

Best,

Maurice

Re: year, month ?

HirisH_V7 — Sat, 19 Mar 2016 09:31:44 GMT

Hi,

I hope your Data is like this,

Data:

LOAD *,

Date(Date#('01'&'/'& Month & '/'&Year,'DD/MMM/YYYY'),'DD/MM/YYYY') as Date

INLINE [

Month, Year, Data, Customer

Jan, 2016, 423, A

Feb, 2016, 233, B

Mar, 2016, 33, C

Apr, 2016, 23, D

May, 2016, 333, E

Jun, 2016, 332, F

Jul, 2016, 33, G

Aug, 2016, 54, H

Sep, 2016, 544, I

Oct, 2016, 4542, J

Nov, 2016, 433, K

Dec, 2016, 22, L

];

Then by using Calendar Script ,Will have the output like this,

Hope this helps ,

PFA,

Hirish