Filtering data through Date format "MM/DD/YY"

[#1]

Hello Guys,

I am a beginner and recently started working on Qlikview. Well I am having a real hard time as I am stuck and don't know how to proceed further. I am loading data from Excel files.

I have a column name as "Start Date" where the dates are entered in the format "MM/DD/YY" eg. 09/08/2011, 08/09/2009 etc. Now my question is

I have to create a bar chart where I have to represent the data yearly like 2009,2010, 2011 etc. I don't know how to count the records yearly , i.e. , counting the total cases in 2009 then 2010 and further? I have to find a solution which can filter the data based on year in the Start Date column and count the total records based on that for every year.

I will appreciate you replies. Thank you.

Yaman

[#2 · Accepted solution]

Hi Yaman,

there is two options to achive this:

- in the script

the idea is to create "Year" field based on the "Start Date" field. The script may look like this:

TableName:

Load

  id,

   StartDate,

   year(date#(StartDate,'DD/MM/YYYY')) as Year

From file.xls

date# function helps QV "understand" in what format is the given date (in your case DD/MM/YYYY). Next "year" function return only the year. In this scenarion you will have field "Year" for each row and in your chart put Year as dimension and count(id) as expression.

-in the chart dimension

the same functions are used ( year(date#(StartDate,'DD/MM/YYYY')) ) but instead in the script plase it in dimension as "Calculated dimension" (the expression is the same). And the result is the same.

Personally i'll add the logic in the script because in this case is possible Year to be added as separate selection or if something is changed in excel file the change will be only in one place not on every chart that have year as dimension

Hope that helps!

Stefan

[Anonymous]

Stefan is right.

If you want to add more calender fields (like Months, weeks etc,) you can add a calender to your script based on (in your case) the "Start Date" field.

Take a look at the script in the attached file. I hope it is usefull.

[#4]

Hello Dennis,

Thank you for the answer. It really helped and I used the other method not the script one.   I have actually not started working on script as of now. Could you please suggest me some ways to get started with. Right now I am able to make simple charts/dashborads. What is the best way to learn the advanced level implementation of the tool like the scripting , connection with an Oracle databse etc?

Thank you again.

Best ,

Yaman Tandon

[#5]

Hello Stefan,

Thank you for the answer. It really helped and I used the other method not the script one.   I have actually not started working on script as of now. Could you please suggest me some ways to get started with. Right now I am able to make simple charts/dashborads. What is the best way to learn the advanced level implementation of the tool like the scripting , connection with an Oracle databse etc?

Thank you again.

Best ,

Yaman Tandon

[#6]

Hi Yaman,

connecting qv to datasource that you are familliar with is a good start. But in my experience the best learing is when i was with customer. Very often customers datasources and requirements are more tricky that i can think of. Another way to connect to data is to use public APIs. For example twitter api (in particular twitter search because no authentication is required) or there is various saites provide with free datasetes (like InfoChimps http://www.infochimps.com/)

Regards!

Stefan

[Anonymous]

Hi Yaman,

If you want to connect to an Oracle database the easiest way is to start is to go to your script (Ctrl +e) and select ODBC below at "Data" ( I assume you can connect to your Database with ODBC right?)

Then Use the "Connect" button and select the requered Data source, fill in Username and Pass word and clik OK

This will create a new line in your script like:

ODBC CONNECT TO DATASOURCE (XUserId is BIWDTHRICaQGC, XPassword is UGAcYHRICaQUH);

This is your connection to your database.

Now use the select button to see your database tables and make selections on whta you wnat to load in your Qlikview document. I would suggest to first start with one smal table, check Precending load, press ok and see what happens. Load the script (Ctrl +r) and start Qliking 🙂

Hope this is the information you are looking for to get started.

Good luck.