4 Replies Latest reply: Sep 13, 2010 11:47 AM by pepps1976 RSS

    Grouping Data

    pepps1976

      Hi all

      I have a couple of questions here i have loaded colums from a table one column is "Quote Date" the other column is "Quote No", Can any body tell me how i can group the Quote Date into months at the moment it displays like 10/08/2010, 11/08/2010, 12/08/2010 etc, so idealy i would like all the days associated with a month Just to appear as August.

      Also the quote No just adds itself up aswell ie: 36704, 36705, 36706 i would like that to be displayes as just 3 quotes.

      So in the Month Of August 3 Quotes were generated.

      Apologise probably for this simple question but i am completely new to all of this.

      Thanks

      John

        • Grouping Data
          Community Administrator

          John,

          First off, welcome to QlikView and our community; we're glad to have you!

          The answer to your first question is the date expressions built into QlikView. For example, if you want a field which only contains the month value of the "Quote Date", use this code in your load script:

           


          Month([Quote Date]) as Month,


          For the second question...QlikView is interpreting the "Quote No" field as a number; it should be treated as text in this case. Try this code:

           


          Text([Quote No]) as [Quote No],


            • Grouping Data
              pepps1976

              Hi Thanks for helping me so i take it i need to re dit the script at the moment i have the following

               

               

               

               

               

               

               

               

               

               

               

               



               

               

              CONNECT

               

               

              TO

              [Provider=VFPOLEDB.1;Data Source=C:\USERS\JOHN.PEPPER\DESKTOP\L_DATA\L DATA\COMP_L.DBC;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];

              SQL

               

               

              SELECT

              `ih_quodate`,

              `ih_quotat`



              FROM

               

               

              ihead;

              could you just point me in the right direction of where i need to put that code, sorry for the novice question but i am new to all of this.

               

              John





                • Grouping Data
                  Community Administrator

                  John,

                  Try this:

                   


                  LOAD
                  Month(ih_quodate) as [Quote Month],
                  Text(ih_quotat) as [Quote No];
                  SQL Select * from ihead;


                    • Grouping Data
                      pepps1976

                      Thanks for the great info

                      I have done what you have said and it seems to work, The only thing is i checked on the system for the amount of quotes that were created in Jan 2010 it was 186 and my bar graph displays that correctly however in febuary and the other months it seems to add the amounts of the previous months so for Febuary it saying 440 when its actually a lot less than that, is this a sum problem do you think.??

                      John