Show Straight Table based on List Box

[#1]

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

[johnca]

Hi Borys,

What is your expected output?

[#3]

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

[NickHoff]

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; 

[Anonymous]

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

[Anonymous]

Indeed...

[#7]

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

Thanks!

[Anonymous · Accepted solution]

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.

[#9]

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

[Anonymous]

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;