topic Re: Qlikview Input Box Date Parameters - Show No Rows When No Rows Satisfy Criteria in QlikView

Qlikview Input Box Date Parameters - Show No Rows When No Rows Satisfy Criteria

rebelfox — Wed, 25 Nov 2020 16:16:04 GMT

I have a problem with Qlikview input box entered parameters.

I have a Qlikview document showing one row per in a simple table box with a 'next invoice date' column.

I have added input box parameters to select a 'from invoice date' and a 'to invoice date'.

The input boxes are used with variables and the Document properties 'Triggers' option to filter selected rows.

It works fine with one problem.

If there are no rows that satisfy the 'from date' and 'to date' all rows are listed in my simple table box??!.

How do I get the table box to show no rows when no rows satisfy the criteria?

I have attached a cut down example document.

Help!

Re: Qlikview Input Box Date Parameters - Show No Rows When No Rows Satisfy Criteria

zhadrakas — Fri, 28 Jul 2017 10:43:34 GMT

you will get all data because you are generating an invalid search string by selecting 03/07/2017 because there is no data for this date.

you could add a master calender to your script to get this done as expected.

Try to add a new tab in your script with this code:

This will fill all missing Dates from your min(Next_invoice_date) to the last day of the current year.

temp:
LOAD min(Next_Invoice_Date) as MinDate,
max(Next_Invoice_Date) as MaxDate
RESIDENT CONTRACT_HEADERS;

LET varMinDate = Num(Peek('MinDate',-1,'temp'));
//LET varMaxDate = Num(Peek('MaxDate',-1,'temp')); //take max date of your table
LET varMaxDate = Num(MakeDate(year(today()), 12, 31 )); // take max date of the current year
LET varToday = num(today());
drop table temp;

TempCalendar:
LOAD
$(varMinDate)+rowno()-1 AS DateNumber,
date($(varMinDate)+ rowno() -1) AS
TempDate
AUTOGENERATE
$(varMaxDate)-$(varMinDate)+1;

MasterCalendar:
LOAD
TempDate as Next_Invoice_Date
//Week(TempDate) as Week,
//Year(TempDate) as Jahr,
//Month(TempDate) as Monat,
//Day(TempDate) as Tag,
//Weekday(TempDate) as Wochentag,
//'Q'&ceil(month(TempDate)/3) AS Quartal,
//Date(monthstart(TempDate),'MM.YYYY') AS MonatJahr,
//Week(TempDate)&'-'&WeekYear(TempDate) AS WocheJahr,
//WeekYear(TempDate)*100+Week(TempDate) AS JahrWoche,
//InYearToDate(TempDate,$(varToday),0)*-1 AS CurYTDFlag,
//InYearToDate(TempDate,$(varToday),-1)*-1 AS LastYTDFlag
Resident TempCalendar
ORDER BY TempDate ASC;

//cleanup
DROP Table TempCalendar;
DROP table MaxMin;

LET varMinDate =;
LET varMaxDate =;
LET varToday =;

Re: Qlikview Input Box Date Parameters - Show No Rows When No Rows Satisfy Criteria

rebelfox — Fri, 28 Jul 2017 11:51:54 GMT

It's a bit of an oblique way of doing it but it certainly works.

Many thanks.

Friendly virtual way of gratitude.