Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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
];
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
Hi,
No problems at all, If you could just mark my answer correct as you will close the thread
Mark