topic After using distinct, duplication in date field in QlikView

After using distinct, duplication in date field

Mon, 30 May 2011 15:10:36 GMT

Hi All

In the attached application though I wrote the script to take only the distinct values for 'date' field it is giving lots of duplicate values. Apart from the repeatation of dates there are also issues as given below.

Also if I select From Date and To Date it is giving one date lesser than what I have selected.

For example:

If I select 30/05/2011 in the From Date and 03/06/2011 in the End Date, It selects the date from 30/05/2011 to 02/06/2011

Apart from this there may be some other issues as well. But firstly would like to solve above 2 issues.

Can some one please help me out on this please.

After using distinct, duplication in date field

erichshiino — Mon, 30 May 2011 15:14:39 GMT

You have duplicated values because you still have the timestamp behind your dates. I mean, you have, for example, for 26/05 at 12 and and 7:00. That's will you get two lines.

On your script, instead of date() , you can use daystart() to send your timestamps to date at midnight.

Hope it helps,

Erich

After using distinct, duplication in date field

Mon, 30 May 2011 15:36:11 GMT

Hi Erich

Thanks for your help! But do wants me to do like below?

Date(daystart(DATE(ImplStart+IterNo()-1))) as date

If the above code is correct requesting you to reply for one more problem which I have mentioned in my post.

Thanks in advance! Waiting for your reply...

After using distinct, duplication in date field

erichshiino — Mon, 30 May 2011 19:44:06 GMT

I think this script should work.

I guess that your second problem is related to the first one, but you can correct it even without changes in your script.

Replace the search string of your selection action to this:

=if(vStartDate > 0,'>=' & date(vStartDate, '$(DateFormat)')) & if(vEndDate >0,'<' & date(vEndDate+1,'$(DateFormat)'))

Regards,

Erich

After using distinct, duplication in date field

Tue, 31 May 2011 04:50:51 GMT

Hi Erich

Thanks for your help!

2nd problem has been resolved with the help of solution that you have provided. But I am still unable to 1st problem yet. After using the below code though the values are not getting repeated I am getting incorrect result in my report. Which is not yet included in the sample application which I have attached here. Problem is before using the code it displaying 15 rows in the report and after using your report it is displaying only 2 rows which is defiently incorrect. I think you will be able to understand once I share that report with you.

code]

Date(daystart(DATE(ImplStart+IterNo()-1))) as date

[/code]

Regards

Attitude

Re: After using distinct, duplication in date field

rahulgupta — Tue, 31 May 2011 05:44:57 GMT

Hey hi,

This would be your solution for the 1st issue.

Just use this in the back end if you want.

Re: After using distinct, duplication in date field

Tue, 31 May 2011 06:15:53 GMT

Hi Rahul

Thanks for your help and effort. Could you please tell me where exactly I have to do the changes in the below script.

[code]

LOAD DISTINCT

DATE(ImplStart+IterNo()-1) AS date

RESIDENT Data

WHILE ImplStart+IterNo()-1<=ImplEnd;

[\code]

I did the changes in the below script but it is not giving the range of dates. It is displaying only those dates which are available. For example min(implstart) is 01/01/2011 and max(implend) is 15/01/2011 then it should display all the dates from 01/01/2011 to 15/01/2011. But currently if I do the below changes it is displaying only those dates which are available. It is not displaying range of dates. I think once I attach the sample application you can understand the problem easily.

Also I feel that if we do some changes above script then it will display the range of dates.

[code]

Load *,

Week(date) as Week,

Year(date) as Year,

WeekName(date) as WeekName,

MonthName(date) as MonthName,

WeekDay(date) as WeekDay,

Date(DayStart(date)) as DayStart

;

[\code]

Re: After using distinct, duplication in date field

rahulgupta — Tue, 31 May 2011 08:31:21 GMT

Hi,

In case of specified MaxDate and MinDate, you can use the following lines of code in your script which will generate the range of dates within your maxdate and mindate defined.

/////////////////////Start Of the Calender Script////////////////////////////

set vdate=today();

TEMP:

LOAD

num(min([Common Date])) AS MinDate,

num(max([Common Date])) AS MaxDate

RESIDENT [COMMON INFO];

LET vMinDate = peek('MinDate', 0, 'TEMP');

LET vMaxDate = peek('MaxDate', 0, 'TEMP');

DROP TABLE TEMP;

DateIslandTEMP:

LOAD

date($(vMinDate) + rowno() - 1) AS Date,

year($(vMinDate) + rowno() - 1) AS Year,

month($(vMinDate) + rowno() - 1) AS Month,

date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MonthYear,

num(month($(vMinDate) + rowno() - 1)) AS Month#,

year(yearname(date(($(vMinDate) + rowno() - 1), 'DD/MM/YYYY'),0,4)) AS [Fiscal Year]

AUTOGENERATE vMaxDate - vMinDate + 1;

/////End of the Script///

Hope this helps you.

Re: After using distinct, duplication in date field

Tue, 31 May 2011 09:26:24 GMT

Hi Rahul

Thanks for your effort it is indeed giving the date of range(Displaying all the dates from 'implstart' to 'implend'). But when I use that in my report it is not displaying all the reports as I have dragged the dimension in to the top of the expression. So whatever the dates that are available in the dimension that only is getting displayed. That you will understand only when I attached the app.

But from and to calendar stopped working once I use the code that you have given. When you find time please look into it. As anyway I am looking into that.

Regards

Attitude

Re: After using distinct, duplication in date field

Tue, 31 May 2011 15:13:56 GMT

Hi Erich and Rahul

Have attached the file for your reference. Hope it will be easy for you understand the issue which I am facing. If you have any doubts then please do let me know.

Re: After using distinct, duplication in date field

rahulgupta — Wed, 01 Jun 2011 10:02:33 GMT

Hey Hi,

Can you please reload this application as i have done some changes in the script.

After checking, let me know if you got the desired output or not.

Regards

Re: After using distinct, duplication in date field

Wed, 01 Jun 2011 18:12:19 GMT

Hi Rahul

Thanks for your effort. But after reloading the data it is throwing the below error. Could you please check and let me know whats the problem is?

Field not found - <date>
LOAD
 date(40688 + rowno() - 1) AS Date,
 year(40688 + rowno() - 1) AS Year,
 month(40688 + rowno() - 1) AS Month
AUTOGENERATE vMaxDate - vMinDate + 1

Re: After using distinct, duplication in date field

Wed, 01 Jun 2011 18:22:22 GMT

Hi Rahul

Attached the excel sheet which I am using it. Hope you can reload it to get to know the problem.

Re: After using distinct, duplication in date field

rahulgupta — Thu, 02 Jun 2011 05:08:09 GMT

Hello,

PFA

Check out this and let me know any issues.

Regards.

Re: After using distinct, duplication in date field

Fri, 03 Jun 2011 07:27:16 GMT

Hi Rahul

Thanks!

But still the problem exists. As I told the thing is that in the report that date field in the group should contain all the dates. There shouln't be any breakage of dates. Though the duplicates has been avoided. It is not displaying all the dates. Even the dates which is not there in between Start Date and End Date. Hope it is understandable now. If any doubts please let me know.

Re: After using distinct, duplication in date field

deepakk — Fri, 03 Jun 2011 12:14:11 GMT

hi Attitude,

try the attach application

Deepak