Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a bit of a tricky qlikview problem I was hoping I could get some help with.
I have a table that looks something like this
Document No | Vendor | Material Number | Date |
1 | Vendor A | 100 | 01.01.2015 |
2 | Vendor A | 100 | 02.01.2015 |
3 | Vendor B | 100 | 02.01.2015 |
4 | Vendor B | 101 | 02.01.2015 |
5 | Vendor C | 103 | 02.01.2015 |
6 | Vendor A | 103 | 03.01.2015 |
7 | Vendor A | 102 | 03.01.2015 |
8 | Vendor C | 102 | 04.01.2015 |
9 | Vendor C | 102 | 05.01.2015 |
10 | Vendor C | 101 | 05.01.2015 |
11 | Vendor D | 102 | 06.01.2015 |
12 | Vendor A | 100 | 06.01.2015 |
13 | Vendor B | 100 | 07.01.2015 |
14 | Vendor B | 101 | 07.01.2015 |
15 | Vendor D | 100 | 08.01.2015 |
16 | Vendor C | 103 | 10.01.2015 |
17 | Vendor C | 103 | 10.01.2015 |
18 | Vendor C | 103 | 12.01.2015 |
19 | Vendor D | 102 | 13.01.2015 |
20 | Vendor D | 102 | 14.01.2015 |
What I would like to do is to build a straight table in qlikview which only shows the rows where a certain combination for Vendor and Material Number occurs at least twice and where the dates between those values lies within a certain range defined by a variable.
For example if I set the variable to 1 it would show the following results (sorted by Vendor and Material Number):
Document No | Vendor | Material Number | Date |
1 | Vendor A | 100 | 01.01.2015 |
2 | Vendor A | 100 | 02.01.2015 |
8 | Vendor C | 102 | 04.01.2015 |
9 | Vendor C | 102 | 05.01.2015 |
19 | Vendor D | 102 | 13.01.2015 |
20 | Vendor D | 102 | 14.01.2015 |
Then if I would set the variable to 5 it would show this ( again sorted by Vendor and Material Number; I marked additional entries in yellow):
Document No | Vendor | Material Number | Date |
1 | Vendor A | 100 | 01.01.2015 |
2 | Vendor A | 100 | 02.01.2015 |
12 | Vendor A | 100 | 06.01.2015 |
3 | Vendor B | 100 | 02.01.2015 |
13 | Vendor B | 100 | 07.01.2015 |
4 | Vendor B | 101 | 02.01.2015 |
14 | Vendor B | 101 | 07.01.2015 |
8 | Vendor C | 102 | 04.01.2015 |
9 | Vendor C | 102 | 05.01.2015 |
16 | Vendor C | 103 | 10.01.2015 |
17 | Vendor C | 103 | 10.01.2015 |
18 | Vendor C | 103 | 12.01.2015 |
19 | Vendor D | 102 | 13.01.2015 |
20 | Vendor D | 102 | 14.01.2015 |
Any help on how to achieve this would be greatly appreciated
Lukas
See attached example.
Thanks alot! That worked perfectly
Hi,
May be check the Attachment..
Great. Would you mind marking this discussion as answered?
Hi Lukas,
I Believe you got an answer. Instead of 'Assumed Answer', You can mark it as 'Correct Answer' Button.
Hell Gysbert,
thanks again for your Answer. I just couldnt really wrap my head around the expression and why it works:
count({<Key={"=count(distinct [Document No])>1"},DiffNext={'<=$(vInterval)'}>+<Key={"=count(distinct [Document No])>1"},DiffPrevious={'<=$(vInterval)'}>}DISTINCT [Document No])
Could you maybe elaborate a bit ón what these different set analyses do?
Thanks alot
Lukas