topic Re: Show Straight Table based on List Box in QlikView

Show Straight Table based on List Box

Wed, 28 Jan 2015 16:01:50 GMT

Hello There,

First of all, I have to say that this topic maybe has been discussed before, but I have two days without solve it.

I have one straight table wich I want to be dinamically loaded based on 3 different list boxes (year, month and day).

I have created 3 different variables to store the values of this fields, but I can not make that the straight table works.

I have attached my QV file so you can have an idea of my issue.

Any help will be very appreciated.

Thanks in advance,

Borys

Re: Show Straight Table based on List Box

johnca — Wed, 28 Jan 2015 20:24:32 GMT

Hi Borys,

What is your expected output?

Re: Show Straight Table based on List Box

Wed, 28 Jan 2015 20:33:04 GMT

Hello John,

In the Table Box called "Movimientos" I have all the reading from the entrance access to a building, if I select a year, month and day, I get the total amount of this transacctions for that criteria, in the straight table I have the number of readings but just count one reading per day and IDCard so I can have an idea of the number of people which actually entry the building.

I want that the straight table show just the records from the year, month and day selected in the list boxes for that fields.

Please let me know if you need some additional information, any suggestion will be welcomed.

Thanks,

Borys

Re: Show Straight Table based on List Box

NickHoff — Wed, 28 Jan 2015 20:37:50 GMT

Your problem is because there are no associations being made in your data model. Since you are working with dates, you'll want a master calendar in your application. Create the association between the fact tables date, and the master calendar by aliasing the master date field.

Here is a master calendar you can use, you'll have to adjust the dates based on your data set:

//Calendar:

LET vDateMin = Num(MakeDate(2010,1,1));
//LET vDateMax = Floor(MonthEnd(Today()));
LET vDateMax = Num(MakeDate(2020,6,30));
LET vDateToday = Num(Today());
LET PD = Date(makedate(2010,1,15));
LET vFactor = 6; //offset for calculating fiscal year

TempCalendar:
//Left Keep (PayCore)
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

SCM_CALENDAR_MASTER:
LOAD
Date(TempDate) AS CalendarDate,
Date(TempDate) AS %DATE_KEY,

// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Year(TempDate) AS CalendarYear,

// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,

// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays,

//Fiscal Dates
Num(Month(AddMonths(TempDate,$(vFactor))),00) AS FiscalMonthNum,
'FY ' & Right(Year(AddMonths(TempDate,$(vFactor))),2) AS FiscalYearDesc,
Year(AddMonths(TempDate,$(vFactor)))&'|'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as %Fiscal_Key,
Year(AddMonths(TempDate,$(vFactor)))&'-'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as FYRPR,
Year(AddMonths(TempDate,$(vFactor))) as FiscalYear,
'FY ' & Right(Year(AddMonths(TempDate,$(vFactor))),2)& ' Q' & Ceil(Month(AddMonths(TempDate,$(vFactor)))/3) AS FiscalYearQuarter

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Re: Show Straight Table based on List Box

Anonymous — Wed, 28 Jan 2015 20:38:29 GMT

I guess you get what you want if you remove the variables from the straight table dimensions (?)

Re: Show Straight Table based on List Box

Anonymous — Wed, 28 Jan 2015 20:39:16 GMT

Indeed...

Re: Show Straight Table based on List Box

Wed, 28 Jan 2015 20:43:29 GMT

Let me give it a try and come back please...

Thanks!

Re: Show Straight Table based on List Box

Anonymous — Wed, 28 Jan 2015 20:51:27 GMT

The right thing to do is to rename "Movimientos Torniquete.DIA" and PersonaVisitante.DIA to the same name, I'd use DIA. It will link two tables together, and the table will work.

Variables are not needed.

Re: Show Straight Table based on List Box

Wed, 28 Jan 2015 20:52:28 GMT

Another thing is that the two tables are not linked even though they have the same columns name...

Re: Show Straight Table based on List Box

Anonymous — Wed, 28 Jan 2015 20:57:08 GMT

There are no columns with the same name, because of QUALIFY *

All you need to do to link them by day is to add

UNQUALIFY DIA;

Re: Show Straight Table based on List Box

Wed, 28 Jan 2015 20:59:33 GMT

And the same for year and month? Where I do that? In the script editor?

Re: Show Straight Table based on List Box

Anonymous — Wed, 28 Jan 2015 21:04:16 GMT

No, only for day, or you'll get synthetic keys. Probably you need year and month only in one of the tables.

Yes, in script, right after QUALIFY *;

Re: Show Straight Table based on List Box

Wed, 28 Jan 2015 21:51:46 GMT

Thanks a lot Mike! It works well!