Grouping Data

[#1]

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

[#2 · Accepted solution]

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],

[#3]

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

[#4]

John,

Try this:

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

[#5]

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