topic Re: Show Changes by minimum date in QlikView

Show Changes by minimum date

sjhussain — Mon, 28 Nov 2016 09:01:00 GMT

Hello,

Hope everyone is fine.

We have a scenario in which we would like to show the changes by minimum dates.

For example we have the data as follows:

Date, Name, Status

10/31/2016, John, Active

11/01/2016, John, Active

11/02/2016, John, InActive

11/03/2016, John, InActive

11/04/2016, John, Active

11/05/2016, John, Active

11/06/2016, John, Active

11/07/2016, John, InActive

11/08/2016, John, Active

Now the records which we would like to show are as follows:

10/31/2016, John, Active

11/02/2016, John, InActive

11/04/2016, John, Active

11/07/2016, John, InActive

11/08/2016, John, Active

So we are taking the minimum date of each change. Will appreciate if someone could assist.

Thanks.

Re: Show Changes by minimum date

Chanty4u — Mon, 28 Nov 2016 09:07:10 GMT

try

=FirstSortedValue(Date,Staus)

=FirstSortedValue(Staus,Date)

Re: Show Changes by minimum date

MK_QSL — Mon, 28 Nov 2016 09:08:39 GMT

SET DateFormat='MM/DD/YYYY';

Temp:
Load * Inline
[
  Date, Name, Status
  10/31/2016, John, Active
  11/01/2016, John, Active
  11/02/2016, John, InActive
  11/03/2016, John, InActive
  11/04/2016, John, Active
  11/05/2016, John, Active
  11/06/2016, John, Active
  11/07/2016, John, InActive
  11/08/2016, John, Active
];


Left Join (Temp)
Load 
  Date, 
  Name, 
  Status,
  If(Name <> Previous(Name), 1, If(Status <> Previous(Status), 1,0)) as Flag
Resident Temp 
Order By Date, Name;

Now you can use Flag = 1 which will give you required fields..

If you want to remove all lines except flag 1 use below also.

NoConcatenate

Final:

Load * Resident Temp Where Flag = 1;

Drop Table Temp;

Re: Show Changes by minimum date

sjhussain — Mon, 28 Nov 2016 09:16:13 GMT

Dear Chanty,

Thanks for the prompt response.

How would I use firstSortedValue in the load script?

Re: Show Changes by minimum date

Mon, 28 Nov 2016 09:17:37 GMT

t1:

LOAD *,RowNo() as Key INLINE [

Date, Name, Status

10/31/2016, John, Active

11/01/2016, John, Active

11/02/2016, John, InActive

11/03/2016, John, InActive

11/04/2016, John, Active

11/05/2016, John, Active

11/06/2016, John, Active

11/07/2016, John, InActive

11/08/2016, John, Active

];

Left Join

LOAD Key+1 as Key,Status as Status1 Resident t1;

NoConcatenate

LOAD * Resident t1 Where Status<>Status1;

DROP Table t1;

DROP Fields Key,Status1;

Re: Show Changes by minimum date

sjhussain — Mon, 28 Nov 2016 09:18:09 GMT

Manish,

Thanks for the prompt response.

I will try your solution but wanted to know whether it is possible to use group in this case? I tried using it but the problem is that i am not able to get the changes. I only get one minimum value per change.

Sincerely,

Syed Jawwad Hussain

Re: Show Changes by minimum date

MK_QSL — Mon, 28 Nov 2016 09:23:04 GMT

Here status is changing more than once per Name so using a Group By with Name and Status is not a good idea.

Re: Show Changes by minimum date

sjhussain — Mon, 28 Nov 2016 09:25:37 GMT

Dear Manish,

I have about 20 Million+ records. I will try and see how long it takes with this solution.

Thanks.

Re: Show Changes by minimum date

MK_QSL — Mon, 28 Nov 2016 09:28:08 GMT

Creating a flag with such big records will always be tough wrt to performance.. But even though if you use Group by (Don't know how in this scenario), it will also little performance killing. But Front end you will get better response as flag creation is in back end.