topic Re: storing in mastertable in QlikView

storing in mastertable

Mon, 08 Feb 2016 21:28:31 GMT

I have a column newdate. I want to create a mastercalendar and store the frequency of dates (if a date occurs 3 times in new date column its frequency should be 3, if there is no entry for a particular day its frequency should be zero). The purpose of doing this is i want to make bar chart and line chart of number of tweets per day, per month etc. So i want to store data in mastercalendar. The bar should show zero for a particular day if there was no date entry in new date column on that day and 3 if the date is three times in new date column.

Re: storing in mastertable

MarcoWedel — Mon, 08 Feb 2016 22:52:19 GMT

please post sample data and exprected result. Best as a QlikView application.

thanks

regards

Marco

Re: storing in mastertable

Tue, 09 Feb 2016 13:55:27 GMT

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(onlydate) as minDate,

max(onlydate) as maxDate

Resident newtab;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

is this master calendar right? i have dates from 7/10/2015 till 2/8/2016 in my database but it creates master calendar only from 12/29/2014 till 2/26/2015

Re: storing in mastertable

maxgro — Tue, 09 Feb 2016 21:54:31 GMT

add a trace after drop table Temp to check the min e max date

let v1=date($(varMinDate));

let v2=date($(varMaxDate));

trace varMinDate = $(v1);

trace varMaxDate = $(v2);

Re: storing in mastertable

MarcoWedel — Wed, 10 Feb 2016 23:35:59 GMT

please post some values of your field "onlydate" in table "newtab".

thanks

regards

Marco

Re: storing in mastertable

Thu, 11 Feb 2016 17:46:12 GMT

Hey I myself figured out the solution and solved it. Thank you for help.