Get previous values for a row using Set Analysis

Mon, 16 Nov 2015 14:51:18 GMT

Hi!

I have a doubt about "set analysis" and if I can use it to solve my problem. This is the scenario:

I have a set of documents which belong to a group and each document has an emission date, like:

Group Document Emission Date

----------- --------------- ----------------------

1 DOC 1 01/01/2015

1 DOC 2 01/02/2015

1 DOC 3 01/03/2015

1 DOC 4 01/04/2015

2 DOC 5 01/05/2015

2 DOC 6 01/06/2015

2 DOC 7 01/07/2015

2 DOC 8 01/08/2015

......

For each group I have four documents and for each document I would like to assign the date of the previous document and its name, for example, in the group 1 and 2 the result would be:

Group Document Emission Date Previous Emission Date Previous Document Name

----------- --------------- ---------------------- ------------------------------------ -----------------------------------------

1 DOC 1 01/01/2015 -

1 DOC 2 01/02/2015 01/01/2015 DOC 1

1 DOC 3 01/03/2015 01/02/2015 DOC 2

1 DOC 4 01/04/2015 01/03/2015 DOC 3

2 DOC 5 01/05/2015 -

2 DOC 6 01/06/2015 01/05/2015 DOC 5

2 DOC 7 01/07/2015 01/06/2015 DOC 6

2 DOC 8 01/08/2015 01/07/2015 DOC 7

I have been able to get these previous values with a SQL query filtering by a group but when I try to execute the query for all cases, the query doesn't end so this solution is not viable.

Is there any way to get this using "Set Analysis" or another way different from using SQL query?

Thanks in advance.

Best regards,

Lucía.

Re: Get previous values for a row using Set Analysis

Mark_Little — Mon, 16 Nov 2015 15:39:38 GMT

Hi,

I would do this in script. Look below and then follow the same logic for the previous doc name.

Data:

LOAD *, IF(RowNo()=1,'',

IF(Group=Previous(Group),Previous([Emission Date]))

) as [Previous Emission Date];

LOAD * INLINE [

Group, Document, Emission Date

1, DOC 1, 01/01/2015

1, DOC 2, 01/02/2015

1, DOC 3, 01/03/2015

1, DOC 4, 01/04/2015

2, DOC 5, 01/05/2015

2, DOC 6, 01/06/2015

2, DOC 7, 01/07/2015

2, DOC 8, 01/08/2015

];

Re: Get previous values for a row using Set Analysis

Mon, 16 Nov 2015 16:03:05 GMT

Hi Mark,

Thank you very much for answering. I am new to Qlikview.

I have added the logic to my script and it works fine

Best regards,

Lucía

Re: Get previous values for a row using Set Analysis

Mark_Little — Mon, 16 Nov 2015 17:16:49 GMT

Hi,

No problems at all, If you could just mark my answer correct as you will close the thread

Mark

topic Re: Get previous values for a row using Set Analysis in QlikView

Get previous values for a row using Set Analysis

Re: Get previous values for a row using Set Analysis

Re: Get previous values for a row using Set Analysis

Re: Get previous values for a row using Set Analysis