Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have calendar script as below in qvw.
SortMap:
MAPPING LOAD * INLINE [
Month, MonthSort
Jul, 1
Aug, 2
Sep, 3
Oct, 4
Nov, 5
Dec, 6
Jan, 7
Feb, 8
Mar, 9
Apr, 10
May, 11
Jun, 12
];
Calendar:
LOAD Date as DateEntered,
Date_TS,
Today,
Week,
Year,
Text(Month) as Month,
ApplyMap('SortMap',Text(Month)) as MonthSort,
Day,
WeekDay,
Quarter,
MonthYear,
WeekYear,
CurYTDFlag,
LastYTDFlag,
FiscalYear,
FiscalYearStart,
FiscalWeek,
FiscalWeekSerial,
FiscalQuarter
FROM
[..\QVD\MasterCalendar.qvd]
(qvd);
CurrentFinancialWeek:
LOAD Year,Week,FiscalWeek,FiscalWeekSerial,FiscalYear,FiscalYearStart RESIDENT Calendar WHERE Week=(Week(Today())) and Year=Year(Today())
Order BY Year ASC;
LET curFiscalWeek=Peek('FiscalWeek',0,CurrentFinancialWeek);
LET curFiscalYear=Peek('FiscalYear',0,CurrentFinancialWeek);
LET curWeek=Peek('Week',0,CurrentFinancialWeek);
LET curYear=Peek('Year',0,CurrentFinancialWeek);
LET curFiscalWeekSeq=Peek('FiscalWeekSerial',0,CurrentFinancialWeek);
LET curFiscalYearStart=Peek('FiscalYearStart',0,CurrentFinancialWeek);
DROP TABLE CurrentFinancialWeek;
There are variables in qvw as below
curWeek
curFiscalWeek
curFiscalWeekSeq
curFiscalYearStart
curFiscalYear
curYear
there is pivot table with below dimension and expressions
Dimension : =If(MIXMATCH(Order,'IN','OUT','EXPIRE')=0,Order)
Expression 1: COUNT(DISTINCT{$<Flag={1},Year=,FiscalWeekSerial={$(=(curFiscalWeekSeq)-4)}>}ID)
Expression 2: COUNT(DISTINCT{$<Flag={1},Year=,FiscalWeekSerial={$(=(curFiscalWeekSeq)-3)}>}ID)
Expression 3: COUNT(DISTINCT{$<Flag={1},Year=,FiscalWeekSerial={$(=(curFiscalWeekSeq)-2)}>}ID)
Expression 4: COUNT(DISTINCT{$<Flag={1},Year=,FiscalWeekSerial={$(=(curFiscalWeekSeq)-1)}>}ID)
the table is not showing data. showing empty table. also when I checked the variables in settings the variables are not showing values instead showing blanks.
Can anyone suggest why the pivot table is not showing data. is this because of variables not showing values.
is there any wrong in script?
please suggest me.
Thanks.
any suggestions please.
please any help
Amelia D.
Can help it, insert a counter after comma(;). E.g.:
LET rowCurrentFinancialWeek = NoOfRows('CurrentFinancialWeek');
trace 'Row in table CurrentFinancialWeek' & $(rowCurrentFinancialWeek);
P.S.: Start file log in qvw;
Hope it helps!
Thanks. sorry I couldn't able to get . what you want me to add?
It looks to me that the calendar has no data for the current week, so the CurrentFinancialWeek table is empty. This means the variables will not be populated and the expressions will not populate the table.
Check the qvd file to confirm that it contains the correct Year and Week data, and that at least one row is loaded for CurrentFinancialWeek.
Amelia D.
What was the result found?
Thanks,
I have master calendar as below
//--------------------------------------------------------------------------------------
// Generate Master Calendar
//--------------------------------------------------------------------------------------
TRACE Creating Master Calendar;
/* Load transaction TABLE to get earliest and latest dates */
MinMax:
LOAD
Max(TIMESTAMP(SETUP_DATE)) AS MaxDate,
Min(TIMESTAMP(SETUP_DATE)) AS MinDate
FROM
[..\QVD\Tbl_DATA.qvd]
(qvd);
//EXIT SCRIPT;
/* STORE earliest and latest dates in variables */
LET varMinDate = Num(Peek('MinDate', -1, 'MinMax'));
LET varMaxDate = Num(Peek('MaxDate', -1, 'MinMax')+1);
// EXIT SCRIPT;
/* STORE todays date in variable */
LET varToday = Num(Today());
/* Inline TABLE mapping months to quarters */
QuarterMap:
MAPPING LOAD * INLINE [
Month, Quarter
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];
/* Autogenerate every date between varMinDate and varMaxDate while creating calendar fields */
MasterCalendar:
LOAD*,
FiscalYear & '/ W' & FiscalWeek AS FiscalYearWeek;
LOAD*,
YearStart(Date,0,7) AS FiscalYearStart,
YearName(Date,0,7) AS FiscalYear,
AUTONUMBER(Year(Date) & Week(Date)) AS FiscalWeekSerial,
ROUND(num(([Date]+184-(MakeDate(Year(([Date]+184)-mod(([Date]-2),7)+3), 1, (mod(([Date]-2),7)+1) )-10))/7-0.5,'#,##0')) AS FiscalWeek,
'Q' & CEIL(if(num(Month(Date))<7,num(Month(Date))+6,num(Month(Date))-6)/3) AS FiscalQuarter;
LOAD
Timestamp(Date) as Date_TS,
Date#(Date(Date)) AS Date,
IF(Date = Today(),1) AS Today,
week(Date) AS Week,
Year(Date) AS Year,
Month(Date) AS Month,
Day(Date) AS Day,
WeekDay(Date) AS WeekDay,
ApplyMap('QuarterMap', Num(Month(Date)), Null()) AS Quarter,
Date(MonthStart(Date),'MMM-YYYY') AS MonthYear,
Week(Date) & '-' & Year(Date) AS WeekYear,
InYearToDate(Date, $(varToday), 0) * -1 AS CurYTDFlag,
InYearToDate(Date, $(varToday), -1) * -1 AS LastYTDFlag;
LOAD
Date($(varMinDate) + RecNo() - 1) AS Date
AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);
/* DROP Temporary TABLE */
DROP TABLE MinMax;
STORE MasterCalendar INTO C:\Qlikview\QVD\MasterCalendar.qvd;
DROP TABLE MasterCalendar;
//EXIT SCRIPT;
I am sure data exists for current week in SQL database tables.
is there any wrong in above master calendar?
Please help me.
Amelia D.
Ok. In script of first post, just do it.
1. Include between tables Calendar and CurrentFinancialWeek the command Concatenate;
2. After the semicolon the CurrentFinancialWeek table insert Drop Table Calendar..
The fields two tables have even name. So, no loaded the Resident.
Good luck!.
I'm sorry. In item 1 command correct is NoConcantenate.