Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

12 Replies
johnca
Specialist
Specialist

Hi Borys,

What is your expected output?

Not applicable
Author

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
Specialist
Specialist

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; 

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Indeed...

Not applicable
Author

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

Thanks!

Anonymous
Not applicable
Author

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.

Not applicable
Author

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

Anonymous
Not applicable
Author

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;