topic Basic date input query in QlikView

Basic date input query

Fri, 29 Oct 2010 10:12:12 GMT

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

Basic date input query

Fri, 29 Oct 2010 11:05:50 GMT

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.

Basic date input query

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 11:10:58 GMT

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.

Basic date input query

hopkinsc — Fri, 29 Oct 2010 11:17:00 GMT

Hi,

Take a look at this thread...

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

Basic date input query

Fri, 29 Oct 2010 11:44:27 GMT

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

Basic date input query

hopkinsc — Fri, 29 Oct 2010 12:11:25 GMT

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

Basic date input query

hopkinsc — Fri, 29 Oct 2010 12:24:58 GMT

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

Basic date input query

hopkinsc — Fri, 29 Oct 2010 12:29:46 GMT

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

Basic date input query

hopkinsc — Fri, 29 Oct 2010 12:47:15 GMT

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.

Basic date input query

Fri, 29 Oct 2010 15:20:10 GMT

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.

Basic date input query

hopkinsc — Fri, 29 Oct 2010 15:27:38 GMT

Ok, like i said, the trigger expressions on the variables i dont understand, maybe someone else here can tell us what it does exactly?

=if(vStartDate > 0,'>=' & date(vStartDate, '$(DateFormat)')) & if(vEndDate >0,'<=' & date(vEndDate,'$(DateFormat)'))

The expression that is added to your results box should just display results which fall within your chosen date range.

Basic date input query

hopkinsc — Fri, 29 Oct 2010 15:29:42 GMT

Remember to make sure that you change my examples to match your variable names EXACTLY as it is case sensitive...

Basic date input query

Fri, 29 Oct 2010 16:03:51 GMT

hopkinsc - i am confused as to the two separate Expressions that you displayed above:

Where should the following expression be entered?

=if(vStartDate > 0,'>=' & date(vStartDate, '$(DateFormat)')) & if(vEndDate >0,'<=' & date(vEndDate,'$(DateFormat)'))

The last expression you highlighted (the Sum({<Trading Date one - am I correct in thinking this should be in the Edit Expression field on the table that displays the result to the end user?

I am still struggling with the expression I would like for my particular count: ie. count(acciddate) where the accidents recorded between vStartdate and vEnddate. I have tried adapting your Sum expression for my Count to no avail.

Thanks again

Basic date input query

hopkinsc — Fri, 29 Oct 2010 16:23:13 GMT

Hi, that expression goes as a variable trigger.

Document Properties>>Triggers>> variable Event triggers. select your variable, click on add actions (on input), click add, select in field, and enter:

date in the field box

and the expression in the string box.

do this for both variables..

Basic date input query

Fri, 29 Oct 2010 16:47:22 GMT

Thanks Hopkinsc, it looks like everything is fine up to the expression stage in my final box that the user sees. I keep getting error in expression when I am trying to enter the count accident formula between the two variable dates - any ideas? I'll keep trying.

Basic date input query

hopkinsc — Mon, 01 Nov 2010 12:44:55 GMT

Hi, what is the expreesion you are writing?

Basic date input query

Mon, 01 Nov 2010 12:55:14 GMT

Hi hopkinsc,

I have been trying variations on the following (as previously mentioned, I am very new to this so still finding my way around!) but the best I can do is just return the total number.

COUNT ({< acciddate = {">=$(=Date(vStartdate))"}*{"<=$(=Date(vEnddate))"} >}acciddate)

Basically, I just want to count every accident in a given time period as chosen by the user using the 2 calendar options [startdate] & [enddate}, connected to variables vStartdate & vEnddate respectively.

Thanks

Matt

Basic date input query

hopkinsc — Mon, 01 Nov 2010 13:00:53 GMT

Hi Matt,

Could you post a sample so i can see your tables names and field names??

Basic date input query

Mon, 01 Nov 2010 15:18:40 GMT

The table I am looking at is called accidents which contains a field acciddate which is just a date field that an accident occured (and another field which gives me the Home name from another table but that is fine)

I wish to extract the number of accidents that each Home has recorded by counting how many times the acciddate appears.

I have a calendar object [startdate] which is linked to a variable vStartdate . Also have another calendar object [enddate] which is linked to vEnddate. Both variables have the following search string:

=if(vStartdate > 0,'>=' & date(vStartdate, '$(DateFormat)')) & if(vEnddate >0,'<=' & date(vEnddate,'$(DateFormat)'))

I have a chart which I wish to display the total number of accidents for each Home in a given period by the user using the two calendar objects to give a startdate and enddate and its the expression in this chart which I am struggling with to 'connect' to the two calendars (I am writing the expression for this count in Chart Properties/Expressions/Definition.

Previously, I have just used count(acciddate) to give a total for all Accidents by Home which worked fine but now I wish for the user to select variable dates using the calendars.

Thanks again.

Matt

Basic date input query

hopkinsc — Mon, 01 Nov 2010 16:29:57 GMT

Hi Matt,

Strange as its working fine for me. I have just copied your expression into my document and changed the accidate to one of my fields and it worked. What version of QLIKVIEW are you using?