12 Replies Latest reply: Feb 27, 2017 6:49 PM by Aar Kay

# Doubt in Interval Match

I have the below dataset :-

And I want this Output :-

can anyone help me to do this....

• ###### Re: Doubt in Interval Match

Try this:

Table:

F1, F2,F3

1,12/12/2016,90

1,01/01/2017,92

2,01/02/2017,56

2,22/02/2017,59

];

F1,

F2 as StartDate,

If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date]

Resident

Table

Order By

F1,F2 Desc;

Drop Table

Table;

• ###### Re: Doubt in Interval Match

Thanks Aar Kay...

Load F1,Date#(F2,'DD/MM/YYYY') as F2; // why we are using this preceeding Load

and

If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date]

Can you explain me the above line

• ###### Re: Doubt in Interval Match

preceeding load is just to tell qlikview that F2 is date field in 'DD/MM/YYYY' format for the sample data.

If(F1=peek(F1) -this is if field value of F1 is equal to previous row value of F1,

then Date(peek(StartDate)-1,'DD/MM/YYYY') i.e take value of startdate "minus" 1day from  the previousrow

if field value of F1 is "not" equal to previous F1 then return today()

• ###### Re: Doubt in Interval Match

if I want  F3 Column also in the resultant table. for this what I need to do..

• ###### Re: Doubt in Interval Match

Try this:

Table:

F1, F2,F3

1,12/12/2016,90

1,01/01/2017,92

2,01/02/2017,56

2,22/02/2017,59

];

F1,

F2 as StartDate,

If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date],

F3

Resident

Table

Order By

F1,F2 Desc;

Drop Table

Table;

• ###### Re: Doubt in Interval Match

Did you change F1 to *  in the preceding load

Table:

F1, F2,F3

1,12/12/2016,90

1,01/01/2017,92

2,01/02/2017,56

2,22/02/2017,59

];

F1,

F2 as StartDate,

If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date],

F3

Resident

Table

Order By

F1,F2 Desc;

Drop Table

Table;

• ###### Re: Doubt in Interval Match

Below is the code which i tried to execute but gives error :-

Gives the following error :-

• ###### Re: Doubt in Interval Match

Sorry About that because of load * -F2 field is being loaded twice that is why it is giving you error

Use this  Load F1,F3,Date#(F2,'DD/MM/YYYY') as F2;

• ###### Re: Doubt in Interval Match

Thank You So Much Aar Kay. Now It is Working

• ###### Re: Doubt in Interval Match

I have this dataset :-

In this I have to match the OrderDate with the previous table columns of start date and end date and Derive a new field Of Amount which is equals to Price * Qty {Price From the previous table}

and i want this output :-

• ###### Re: Doubt in Interval Match

Using Intervalmatch like below:

Table:

ProductID, F2,F3

1,12/12/2016,90

1,01/01/2017,92

2,01/02/2017,56

2,22/02/2017,59

];

Temp:

ProductID,

F2 as StartDate,

If(ProductID=peek(ProductID),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date],

F3

Resident

Table

Order By

ProductID,F2 Desc;

Drop Table

Table;

A:

OrderID,Order Date,ProductID,Salesrep,Qty

9123,15/12/2016,1,Suresh,6

6635,01/02/2017,2,Ramesh,4

5589,25/02/2017,2,Shubham,3

4698,02/02/2017,1,Ankit,2

];

Inner Join(A)

IntervalMatch([Order Date],ProductID)

StartDate,

[End Date],

ProductID

Resident Temp;

Left Join (A)

StartDate,

[End Date],

ProductID,

F3

Resident Temp;

Drop Table Temp;

Final: