topic Re: Grouping dates in QlikView

Grouping dates

Fri, 24 Apr 2015 11:11:25 GMT

Can anyone please help me. I am trying to group dates (ordheadh_0."o-date") by the month and year from data in a Progress database.

SQL SELECT ordheadh_0."cust-code", ordheadh_0."o-value", ordheadh_0."o-vatam", ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE (ordheadh_0."o-date">={d '2014-01-01'})

GROUP BY MONTH(ordheadh_0."o-date")+"-"+YEAR(ordheadh_0."o-date");

Thanks

Re: Grouping dates

buzzy996 — Fri, 24 Apr 2015 16:42:38 GMT

try tis,

SQL SELECT

ordheadh_0."cust-code",

ordheadh_0."o-value",

ordheadh_0."o-vatam",

ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date#(ordheadh_0."o-date",'YYYY-MM-DD')>='2014-01-01'

GROUP BY MONTH(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

AND YEAR(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'));

Re: Grouping dates

Anonymous — Fri, 24 Apr 2015 16:57:50 GMT

hi try

SQL SELECT ordheadh_0."cust-code",

ordheadh_0."o-value",

ordheadh_0."o-vatam",

ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date(date#(ordheadh_0."o-date¨,'YYYY-MM-DD')>= '2014-01-01'

GROUP BY MONTH(date(date#(ordheadh_0."o-date",'YYYY-MM-DD'), YEAR(date(date#(ordheadh_0."o-date",'YYYY-MM-DD');

Re: Grouping dates

Mon, 27 Apr 2015 08:06:44 GMT

Hi, I've tried both scripts and they return errors I do not understand.

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "Date#(ordheadh_0."o-date",'YYYY-MM-DD')>" (10713)

SQL SELECT ordheadh_0."cust-code",

ordheadh_0."o-value",

ordheadh_0."o-vatam",

ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date#(ordheadh_0."o-date",'YYYY-MM-DD')>='2014-01-01'

GROUP BY MONTH(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

AND YEAR(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

SQL SELECT ordheadh_0."cust-code",

ordheadh_0."o-value",

ordheadh_0."o-vatam",

ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date(date#(ordheadh_0."o-date",'YYYY-MM-DD')>= '2014-01-01'

GROUP BY MONTH(date(date#(ordheadh_0."o-date",'YYYY-MM-DD'), YEAR(date(date#(ordheadh_0."o-date",'YYYY-MM-DD')

Re: Grouping dates

Mon, 27 Apr 2015 08:07:44 GMT

Hi Jannet, I tried your script and get this error. Can you help any further ? Appreciate your assistance.

SQL SELECT ordheadh_0."cust-code",

ordheadh_0."o-value",

ordheadh_0."o-vatam",

ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date(date#(ordheadh_0."o-date",'YYYY-MM-DD')>= '2014-01-01'

GROUP BY MONTH(date(date#(ordheadh_0."o-date",'YYYY-MM-DD'), YEAR(date(date#(ordheadh_0."o-date",'YYYY-MM-DD')

Re: Grouping dates

Mon, 27 Apr 2015 08:08:19 GMT

Hi Shiva, I tried your script and get this error. Can you help any further ? Appreciate your assistance also.

SQL SELECT ordheadh_0."cust-code",

ordheadh_0."o-value",

ordheadh_0."o-vatam",

ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date#(ordheadh_0."o-date",'YYYY-MM-DD')>='2014-01-01'

GROUP BY MONTH(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

AND YEAR(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

Re: Grouping dates

buzzy996 — Mon, 27 Apr 2015 08:47:32 GMT

seems to be here the problem with ODBC drives &bridge!

try to create simply query and fetch data...see?whether ur getting any issue or not?

Re: Grouping dates

Mon, 27 Apr 2015 09:41:27 GMT

Hi, thanks for help.

This was my original query and brings through the data without any errors.

I was just trying to group the information when I then started with the problem.

SQL SELECT ordheadh_0."order",

ordheadh_0."cust-code",

ordheadh_0."o-date",

ordheadh_0."o-value",

ordheadh_0."o-vatam"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE (ordheadh_0."o-date">= '2014-01-01');

Re: Grouping dates

Mon, 27 Apr 2015 10:52:07 GMT

What are you trying to aggregate? Whatever isn't included in the group by part of a sql query must be aggregated in some way. for example... Do you understnad SQL?

SQL SELECT ordheadh_0."cust-code",

sum(ordheadh_0."o-value"),

sum(ordheadh_0."o-vatam"),

cast(datepart(month,ordheadh_0."o-date") as varchar(2)) + ' - ' + cast(datepart(year,ordheadh_0."o-date") as varchar(4)) as mmyyyy,

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE ordheadh_0."o-date" >= '2014-01-01'

GROUP BY ordheadh_0."cust-code", cast(datepart(month,ordheadh_0."o-date") as varchar(2)) + ' - ' + cast(datepart(year,ordheadh_0."o-date") as varchar(4))