topic Re: Script: Getting rid of records depending on date in QlikView

Script: Getting rid of records depending on date

Fri, 16 Oct 2015 13:21:16 GMT

Good afternoon,

I´ve got data like

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 30.06.2015

ValidToDate: 15.06.2015

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 30.06.2015

ValidToDate: 30.06.2015

The records cover several years. How can I get rid of the records that have a ValidToDate that is not the last day of the month? In the example above, I would keep only the second record.

Thanks!

Re: Script: Getting rid of records depending on date

settu_periasamy — Fri, 16 Oct 2015 13:28:07 GMT

Hi,

Try

if(validtodate=Date(MonthEnd(validtodate)),validtodate) as validtodate

Re: Script: Getting rid of records depending on date

Anonymous — Fri, 16 Oct 2015 13:31:49 GMT

you can either Group the data in script

load

PersonnelNr,

Name,

CostCenter,

LastDayofMonth

max(ValidToDate)

from

Group by

PersonnelNr,

Name,

CostCenter,

LastDayofMonth

or you just load the data with the where clause (works only if there is only 1 record per PersonnelNr for validtodate on the last day of month). if a Person can be more than onces in that list till end of month, you should do the group

TmpData:

load *

resident Data

where ValidToDate=monthend(ValidToDate)

drop table Data

ren table TmpData to Data

Re: Script: Getting rid of records depending on date

Fri, 16 Oct 2015 13:46:54 GMT

Thanks but I want to actually eliminate the first record so as to avoid having to always use count distinct. Any suggestions?

Re: Script: Getting rid of records depending on date

Anonymous — Fri, 16 Oct 2015 13:50:34 GMT

using the following would eliminate the first record

TmpData:

load *

resident Data

where ValidToDate=date(monthend(ValidToDate))

drop table Data

rename table TmpData to Data

Re: Script: Getting rid of records depending on date

settu_periasamy — Fri, 16 Oct 2015 13:54:23 GMT

Then you can try the Rudolf Suggestion..

Re: Script: Getting rid of records depending on date

rwunderlich — Fri, 16 Oct 2015 14:00:14 GMT

A couple people have mentioned using:

where ValidToDate=monthend(ValidToDate)

which I think is on the right track. However MonthEnd returns a timestamp corresponding to 23:59:59 so I think the comparison should be:

where ValidToDate=floor(monthend(ValidToDate))

or perhaps:

where ValidToDate=date(floor(monthend(ValidToDate)))

-Rob

Re: Script: Getting rid of records depending on date

Fri, 16 Oct 2015 14:06:37 GMT

Thanks Rudolf. I didn´t explain myself well, apologies. I could have records as per below and would want to keep records 2 and 3 but not 1:

Rec1

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 30.06.2015

ValidToDate: 15.06.2015

Rec2

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 30.06.2015

ValidToDate: 30.06.2015

Rec3

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 31.05.2015

ValidToDate: 30.06.2015

Re: Script: Getting rid of records depending on date

Anonymous — Fri, 16 Oct 2015 14:16:24 GMT

that's exactly what this script is doing (thks Rob for the correction!)

in your example the Rec 2 and 3 will be kept!

give it a try!@

TmpData:

load *

resident Data

where ValidToDate=floor(monthend(ValidToDate))

@robwunderöich

drop table Data

ren table TmpData to Data

Re: Script: Getting rid of records depending on date

Fri, 16 Oct 2015 14:50:10 GMT

Thanks!