Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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


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.

12 Replies
Highlighted
Not applicable

any suggestions please.

Highlighted
Not applicable

please any help

Highlighted
Specialist
Specialist

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!

Highlighted
Not applicable

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

Highlighted

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Specialist
Specialist

Amelia D.

What was the result found?

Highlighted
Not applicable

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.




 

Highlighted
Specialist
Specialist

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

Highlighted
Specialist
Specialist

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