6 Replies Latest reply: Jun 1, 2011 3:36 AM by Dennis Hoogenboom RSS

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

      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

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

          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

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

            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

              • Filtering data through Date format "MM/DD/YY"
                Dennis Hoogenboom

                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.