topic Exposing all combinations of cyclically repeating calendar values in a straight table in QlikView

Exposing all combinations of cyclically repeating calendar values in a straight table

jwaligora — Sat, 16 Nov 2024 21:17:27 GMT

Hi,

For demonstration purposes, I have the following data set:

ID	DT	Event
101	2019-01-01 01:40:00	Blue
102	2019-01-01 12:40:00	Green
103	2019-01-01 12:41:00	Blue
104	2019-01-01 12:41:00	Blue
105	2019-01-01 18:21:00	Grey
106	2019-01-01 23:10:00	Purple
107	2019-02-01 02:50:00	Red
108	2019-02-01 12:33:00	Red
109	2019-02-01 14:00:00	Black
110	2019-04-01 01:30:00	Blue
111	2019-05-01 12:41:00	Black
112	2019-05-01 18:45:00	Red
113	2019-08-01 07:28:00	Blue

What I need QV to display is a table with the headings:

WeekDay

Hour

Record Count

The problem is that I need ALL weekdays (Sun to Sat) to be listed, and for each of the weekdays I need ALL hours (0 to 23) to be listed. 7*24=168 rows in total.

The actual data set is rather large and convoluted, but still subject to "gaps" when filtered, resulting in certain combinations of WeekDay and Hour to not be present in the table. I fudged a solution by adding a concatenation of weekday and hour into my master calendar, and subsequently forcing "Show All Values" on the table dimension. This is non-ideal, because I end up with one dimension column instead of two...and also because there's probably a better way to address this issue.

Thanks,
J.

Re: Exposing all combinations of cyclically repeating calendar values in a straight table

Vegar — Thu, 07 Mar 2019 10:20:13 GMT

You will need to have hour and date in the same calendar table if you are to make any selection in date and/or hours.

The colde below will help you.

Transactions:
LOAD
	ID,
	Event,
	floor(DT) &  '|' & hour(DT) as %calendar,
	DT,
	1 as [No of events];
LOAD ID, 
     Timestamp#(DT, 'YYYY-DD-MM hh:mm:ss') as DT, 
     Event
FROM
	[https://community.qlik.com/t5/QlikView-App-Development/Exposing-all-combinations-of-cyclically-repeating-calendar/m-p/1553259#M440435]
	(html, codepage is 1252, embedded labels, table is @1);

for _date=date#('2019-01-01' , 'YYYY-DD-MM') to date#('2019-08-01', 'YYYY-DD-MM') step 1
	for _hour=1 to 24 step 1
		LOAD  
		$(_date)&'|'&$(_hour) as  %calendar,
		Date($(_date),'YYYY-DD-MM') as Date,
		weekday($(_date))  as Weekday,
		num($(_hour), '00') & ':00' as Hour
		AutoGenerate 1;
	next
next

Below a pivot chart in QlikView presenting the values. Remember to uncheck Supress zero values in the chart properties.

Re: Exposing all combinations of cyclically repeating calendar values in a straight table

jwaligora — Fri, 08 Mar 2019 21:08:11 GMT

Hi,

With this approach (barring user error on my part) even with 'suppress zeros' unchecked, the data table shrinks when filters are applied.

The business need is very specific here - the output must be 3 columns, and 168 rows. Values (column total) must be reflective of selection criteria.

Here's the code for what I'm doing (pretty much same as yours...)

DataTab:
Load 
	ID,
	Timestamp#(DT, 'YYYY-MM-DD HH:mm:ss') as DT,
	Timestamp(Floor(Timestamp#(DT, 'YYYY-MM-DD HH:mm:ss'), 1/24),'YYYY-MM-DD HH') as DH,
	Event;

LOAD ID, 
     Timestamp#(DT, 'YYYY-DD-MM hh:mm:ss') as DT, 
     Event
FROM
	[https://community.qlik.com/t5/QlikView-App-Development/Exposing-all-combinations-of-cyclically-repeating-calendar/m-p/1553259#M440435]
	(html, codepage is 1252, embedded labels, table is @1);


Temp:
Load 
	min(Floor(DT,1/24),'YYYY-MM-DD HH:mm:ss') as minDT,
	max(Ceil(DT,1/24),'YYYY-MM-DD HH:mm:ss') as maxDT	
Resident DataTab;

LET vMinDT = peek('minDT');
LET vMaxDT = peek('maxDT');

Drop table Temp;

LET vDays = vMaxDT - vMinDT ;

MC:
LOAD 
	Timestamp(Floor(TempDate, 1/24),'YYYY-MM-DD HH') as DH,
	Hour(TempDate) as [Hour],
	Date(Floor(Date(TempDate,'YYYY-MM-DD')),'YYYY-MM-DD') as [Day],
	WeekDay(TempDate) as WkDay,
	WeekDay(TempDate) & ' ' & right(Timestamp(Floor(TempDate, 1/24),'YYYY-MM-DD HH'),2) as WkDayHr,
	Dual(Date([TempDate],'YYYY') & '-' & Date([TempDate],'MMM'), MonthStart([TempDate])) as [Month],
	Year(TempDate) as [Year]
;

LOAD
    Timestamp($(vMinDT) + (RecNo()-1)/24) as TempDate
AUTOGENERATE 24 * $(vDays) + 1;

The two approaches that work for me so far (both fudge'y) are:

(1) Create a straight table with Dimension = WkDayHr and Expression = count({$} distinct ID). Set Dimension to "Show All Values", for the Expression uncheck "Suppress Zero Values".

(2) Create a straight table with Dimensions: {WkDay, Hr} and Expression =if (count({1} distinct ID) = count({$} distinct ID), count({$} distinct ID),count({$} distinct ID)) ... yes, it says "if True then apples else apples". For the Expression, uncheck "Supress Zero Values". "Show All Values" does NOT need to be selected for either dimension.

The first one fails to address the business need as it only uses 2 columns, while the second one uses a seemingly unnecessarily convoluted Expression calculation.

Thanks,
J.