Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shwetagupta
Partner - Creator II
Partner - Creator II

Concatenation of tables and Auto Calendar Script

Hi All,

I have concatenated three different tables with same headers. i.e I have concatenated data of April 2014, May 2014 and June 2014.

After loading script i.e

Apr 2014;

concatenate

may2014

Concatenate

June 2014

applied following script of auto calendar

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

  Month($1) AS [Month] Tagged ('$month'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

  Date(Floor($1)) AS [Date] Tagged ('$date');

DERIVE FIELDS FROM FIELDS [DateFieldName] USING [autoCalendar] ;

though I can see Date.autocalendar.year and other similar fields but there is no data in created fields... How can I get week , day, year, yearMonth from the fields

Need Some Urgent help.

2 Replies
Gysbert_Wassenaar

Make sure your date field actually contains date values, not text values that only look like dates. Use the date# function if necessary to convert text values to date values. Perhaps this blog post helps: Why don’t my dates work?


talk is cheap, supply exceeds demand
shwetagupta
Partner - Creator II
Partner - Creator II
Author

Hi Gysbert

Thanks a lot, though my excel sheet field was in Date format but I used date# it solved my problem.

Thanks