Displaying duplicates that fit a certain criteria defined in a variable

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[#3]

Thanks alot! That worked perfectly

[settu_periasamy]

Hi,

May be check the Attachment..

[Gysbert_Wassenaar]

Great. Would you mind marking this discussion as answered?

[settu_periasamy]

Hi Lukas,

I Believe you got an answer. Instead of 'Assumed Answer', You can mark it as 'Correct Answer' Button.

Qlik Community Tip: Marking Replies as Correct or Helpful

[#7]

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