Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the below dataset :-
And I want this Output :-
can anyone help me to do this....
Thank You So Much Aar Kay. Now It is Working
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...
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;