topic Re: Problem with group by clause. in QlikView

Problem with group by clause.

agni_gold — Sun, 08 Jun 2014 10:00:07 GMT

Hi,

I have a data source from where i have fetch the many columns in which one is date column, If i load directly that column to qlikview it will by default changes to number format. but if during loading i am using

DATE(COLUMN,'DD-MMM-YYYY')

it will store date column in DD-MMM-YYYY .

Problem is that when i am applying any calculation on any fields like sum , then during group by

is it will accept my DD-MMM-YYYY format or qlikview numeric format. ?

Re: Problem with group by clause.

IAMDV — Sun, 08 Jun 2014 10:35:08 GMT

Agnivesh - I assume the field is just a Date and not TimeStamp. Is this right? I don't think it matters whether it's Date or numeric format because you can always change the formatting with Preceding Load above your normal Load statement which has Group By clause. I think it will be numeric format because that's the underlying format from the DB.

I hope this helps!

Cheers,

www.QlikShare.com

Re: Problem with group by clause.

swuehl — Sun, 08 Jun 2014 11:55:14 GMT

Agnivesh, I haven't fully understood if this is a general question or you are asking for help with a specific problem.

If you are having a specific problem, please post some more information, some demo data and what your expected ouput is.

In general, applying Date(COLUMN,'DD-MMM-YYYY') on a numeric value of COLUMN will create a dual, keeping the original numeric value and creating a text string that interprets the number as a date (number of days since Dec 30, 1899) and formats the date textual representation according the format code 'DD-MMM-YYYY'.

A group by clause will use the numeric value of that date value, so if the underlying numeric value is more granular than the textual representation (e.g. having a time part, decimals), the outcome may be not what you expect.

Use daystart() or floor() to create pure dates from a timestamp.

If you use the date field in an aggregation like sum(), you might need to reformat the textual representation using Date().

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/13/dual

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

Re: Problem with group by clause.

israrkhan — Sun, 08 Jun 2014 12:06:52 GMT

Hi Agnevish,

if you will write Groupd by Date, than it will group by qlikview numeric format(the default date format which you have in source database),

but you can write your Group by Like,

Group By DATE(COLUMN,'DD-MMM-YYYY')

now first it will format your date and than it will group by DD-MMM-YYYY format.

you can write calculated group by clause,

like group by Year(DateField), it will grouo by same year....

Khan