Basic date input query

[#1]

Hi all,

I have just started playing around with the Personal Edition connecting to an ODBC database and have a basic query on allowing the end-user to search a table based on dates.

For example, I have a table (Accidents) which contains Home & Date fields. I would like the user to be able to see how many Accidents each Home had in a given week (Monday-Sunday). Within the Edit Script command, I can use commands (i.e. where Accidate >= 40469 and Acciddate <= 40475) etc) but the intended end-user would not have access to Edit Script .

After searching on here, I have tried various options such as using the slider/calendar object and entering two Excel tables [startdate] and [enddate] but cannot make it work.

Would appreciate some basic advice please.

Thanks,

Matt

[#2]

You could try, the Calendar Generation script in this qvw file attached.

Replace the below on line 48 with Accidate

Calendar

AS [PUTINYOURDATEFIELD],

Connect up the tables that are on the Main tab to the Accidate field you have created above. Include Accidate in the Charts you are working with.

You can then select by Month, Day, Quarter, Year etc. Just update the script to generate whatever else it is that you want.

[Miguel_Angel_Baeyens]

Hello Matt,

Say you have something similar to the following as data and calendar:

Accidents:LOAD * INLINE [PersonID, Date, HomeA, 22/04/2010, City1B, 21/04/2010, City1C, 26/04/2010, City2D, 22/04/2010, City2E, 23/04/2010, City1F, 21/04/2010, City2G, 26/04/2010, City1H, 22/04/2010, City1A, 23/04/2010, City1F, 21/04/2010, City2]; MinMaxDates:LOAD Min(Date) AS MinDate, Max(Date) AS MaxDateRESIDENT Accidents; LET vMinDate = Peek('MinDate', -1, 'MinMaxDates');LET vMaxDate = Peek('MaxDate', -1, 'MinMaxDates'); DROP TABLE MinMaxDates; TempCalendar:LOAD Date(Num($(vMinDate)) + IterNo()) AS TempDateAUTOGENERATE 1 WHILE Date(Num($(vMinDate)) + IterNo()) <= Date($(vMaxDate)); Calendar:LOAD TempDate AS Date, Month(TempDate) AS Month, WeekDay(TempDate) AS WeekDayRESIDENT TempCalendar; DROP TABLE TempCalendar;

You may use this file objects as a base to check records between two dates.

Hope that helps.

[hopkinsc]

Hi,

Take a look at this thread...

http://community.qlik.com/forums/t/32557.aspx

[#5]

Thanks Lee, Miguel but unfortunately it seems I cannot open any other Qlikview files created by anyone else(I am using the free Personal Edition currently)

Thanks Hopkinsc - I have looked at that already and created two excel spreadsheets [startdate} and [enddate] with 1 column of dates in each and created the 2 calendar options which list the dates from the spreadsheets but the part I am struggling to understand is this :

" Create two variables 'vStartDate' and 'vEndDate' with the following definitions:

vStartDate = GetFieldSelections([Start Date])
vEndDate = GetFieldSelections([End Date]) "

If I rightclick on one of the calendars , the variable option is blank and I cannot see how to edit?

Thanks again all.

Matt

[hopkinsc]

You will first need to create the variables :

Document Properties >> Variables tab

Once the 2 variables are created, you should then be able to select them from the calender properties

[hopkinsc]

Hi Cayfmatt,

I have just looked at how i did my start / end dates. I only used 1 Date table in the script (TradingDate) I have attached a sample, the sample has no data in it but you will be able to see my expressions and variables.

First create the variables - vStartDate, vEndDate

look at Document Properties>> Triggers>> Variable Event Triggers. I have actions assigned to my variables (To be completely honest, im not exactly sure what these actions i do, all i know is that my dates work 🙂

Look at the properties of the two calenders, there is a min and max expression on each.

lastly i have an expression on the 'Transaction Header' box which basiacally only shows information for the date range selected.

I hope this helps....

[hopkinsc]

damn, i just reead that you can't open other documents....

[hopkinsc]

Hi again Matt,

Hope this helps, here are some screen shots of exprssions etc i have used.

Create Variables >> Doc Properties>> Variables

Variables Triggers (on input) >> Doc Properties>>Triggers>>Variables Triggers

Calander Prop>> Calender Properties>> copy whats in image on both Min Vlaue, Max Value (Obviously change the expression to match the field your entering it in. i.e. =Min(TradingDate) goes in Min Value, Max(tradingDate) goes in Max Value. do the same for both calenders.

Expression >> goes in your main chart/box which displays the information.

Obviosly you need to change TradingDate to match your date field.

[#10]

Thanks hopkinsc, your help is much appreciated.

I have set up two Calendars, [startdate] which is set to variable vStartdate and [enddate] set to variable vEnddate with both set to min/max acciddate. I am now trying to understand the expression stage.