Doubt in Interval Match

[Anonymous]

I have the below dataset :-

And I want this Output :-

can anyone help me to do this....

[Anonymous]

Thank You So Much Aar Kay. Now It is Working

[Anonymous]

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 :-

can you please help me in this...

[aarkay29]

Using Intervalmatch like below:

Table:

Load ProductID,F3,Date#(F2,'DD/MM/YYYY') as F2;

LOAD * INLINE [

    ProductID, F2,F3

    1,12/12/2016,90

    1,01/01/2017,92

    2,01/02/2017,56

    2,22/02/2017,59

];

Temp:

Load

    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:

Load * inline [

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)

load

StartDate,

[End Date],

ProductID

Resident Temp;

Left Join (A)

load

  StartDate,

  [End Date],

  ProductID,

  F3

Resident Temp;

Drop Table Temp;

Final:

load

OrderID,[Order Date],ProductID,Salesrep,Qty,Qty*F3 as Amount

Resident A;

Drop TAble A;