Get previous values for a row using Set Analysis

[#1]

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.

[Mark_Little · Accepted solution]

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

];

[#3]

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

[Mark_Little]

Hi,

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

Mark