12 Replies Latest reply: Jan 28, 2015 4:51 PM by Borys Vulgarin RSS

    Show Straight Table based on List Box

      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
          John Cavoulas

          Hi Borys,

           

          What is your expected output?

            • Re: Show Straight Table based on List Box

              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
                  Nick Hoff

                  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
                  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
                    Michael Solomovich

                    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

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

                       

                      Thanks!

                      • Re: Show Straight Table based on List Box
                        Michael Solomovich

                        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

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