topic Re: creating a min date, where values repeat in QlikView

creating a min date, where values repeat

Fri, 28 Aug 2015 10:14:37 GMT

I need to reduce my date to show the minimum date a customer held status

I have a table containg this information,

Customerno FromDate Status

74 30/04/2015 Purchasing

74 08/05/2015 Moving

74 18/05/2015 Moving

74 26/05/2015 Moving

74 01/06/2015 Purchasing

74 08/06/2015 Purchasing

74 12/06/2015 Purchasing

74 26/06/2015 Purchasing

74 08/07/2015 Purchasing

74 20/07/2015 Purchasing

74 26/07/2015 Purchasing

74 02/08/2015 Purchasing

74 09/08/2015 Purchasing

74 16/08/2015 Purchasing

74 23/08/2015 Purchasing

I need to reduce this to;

Customerno FromDate Status

74 30/04/2015 Purchasing

74 08/05/2015 Moving

74 01/06/2015 Purchasing

I've currently got the following in the script;

LOAD
Min(FromDate) AS FromDate,
Customerno,
Status
Resident StatusHistory
Group By Customerno, Status;

which was working ok, but now the Status 'Purchasing' is seen twice, using the min date would exclude the change in behaviour on the 01/06/2015..

I think I need to use peek or previous in the script, but i'm not 100% sure how to go about writting this.

Can anyone help please?

Many thanks in advance

Re: creating a min date, where values repeat

pokassov — Fri, 28 Aug 2015 10:22:58 GMT

t1:

LOAD
FromDate,
Customerno,
Status,

if(Previous(Customerno)=Customerno and Previous(Status)=Status,0,1) As Check
Resident StatusHistory
Group By Customerno, Status, FromDate

Order By Customerno, FromDate, Status;

t2:

load

FromDate,
Customerno,
Status

REsident t1

where Check=1;

Re: creating a min date, where values repeat

Fri, 28 Aug 2015 10:27:42 GMT

Heather, The min should work here. I am suspecting your "DateFormat" environment have different data format against source.

So while loading the data, please use Date# function to convert into date or change the DateFormat

SET DateFormat = 'DD/MM/YYYY' ;

Date(Date#(FromDate,'DD/MM/YYYY')) AS FromDate

Re: creating a min date, where values repeat

pokassov — Fri, 28 Aug 2015 10:36:12 GMT

I didn't get your task first time...

Your script is ok for your task. You have status twice only if Customerno or Status looks like equal but contain different values...For example 'Purchase' and 'Purchase '

Re: creating a min date, where values repeat

Fri, 28 Aug 2015 14:02:00 GMT

Hi Sergey,

This is great, I just had to remove the Group By clause, other than that it seems to be working.

thank you