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

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

Wed, 27 May 2015 12:29:39 GMT

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.

Re: Pivot table not showing data. variables not showing values?

Wed, 27 May 2015 12:42:33 GMT

any suggestions please.

Re: Pivot table not showing data. variables not showing values?

Wed, 27 May 2015 13:02:32 GMT

please any help

Re: Pivot table not showing data. variables not showing values?

jonas_rezende — Wed, 27 May 2015 13:04:01 GMT

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!

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

Wed, 27 May 2015 13:08:10 GMT

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

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

jonathandienst — Wed, 27 May 2015 13:11:09 GMT

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.

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

jonas_rezende — Wed, 27 May 2015 13:22:23 GMT

Amelia D.

What was the result found?

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

Wed, 27 May 2015 13:27:30 GMT

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.

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

jonas_rezende — Wed, 27 May 2015 13:38:27 GMT

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

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

jonas_rezende — Wed, 27 May 2015 13:43:36 GMT

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

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

Wed, 27 May 2015 13:54:01 GMT

Thanks. you mean like this

/*********************************************************************************
//Load Fiscal Calendar
//*********************************************************************************

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 DateLogged,
     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);

NoConcatenate

Drop Table Calendar;

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

jonas_rezende — Wed, 27 May 2015 13:57:43 GMT

Attention in word bold.

NoConcatenate

CurrentFinancialWeek:

LOAD Year,Week,FiscalWeek,FiscalWeekSerial,FiscalYear,FiscalYearStart RESIDENT Calendar WHERE Week=(Week(Today())) and Year=Year(Today())
Order BY Year ASC;

Drop Table Calendar;

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);

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

Wed, 27 May 2015 14:02:10 GMT

Thanks. when I am loading this all sheet tables are showing empty. no data is populating in to qvw.

please help me do I need to change anything more here?