Pivot table not showing data. variables not showing values? urgent help please

[#1]

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.

[#2]

any suggestions please.

[#3]

please any help

[jonas_rezende]

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!

[#5]

Thanks. sorry I couldn't able to get . what you want me to add?

[jonathandienst]

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.

[jonas_rezende]

Amelia D.

What was the result found?

[#8]

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.

[jonas_rezende]

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!.

[jonas_rezende]

I'm sorry. In item 1 command correct is NoConcantenate.