Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Consider the following table:
LOAD * INLINE [
GR, RT, QID, CD, Date
1, 8, 11, 6/1/2017, 8/1/2017
1, 10, 22, 9/1/2017, 8/1/2017
1, 15, 33, 15/1/2017, 8/1/2017
2, 9, 66, 4/1/2017,10/1/2017
2, 12, 44, 8/1/2017, 10/1/2017
2, 15, 55, 11/1/2017, 10/1/2017
];
If Date is greater than CD of the row and less than CD of the next row than that row has to be considered. For the above table i need the following output:
GR | RT | QID | CD | Date |
---|---|---|---|---|
1 | 8 | 11 | 6/1/2017 | 8/1/2017 |
2 | 12 | 44 | 8/1/2017 | 10/1/2017 |
I hope its possible. Help appreciated!
Hi Arpit,
maybe this
Temp:
LOAD *,RowNo() as Rowno INLINE [
GR, RT, QID, CD, Date
1, 8, 11, 6/1/2017, 8/1/2017
1, 10, 22, 9/1/2017, 8/1/2017
1, 15, 33, 15/1/2017, 8/1/2017
2, 9, 66, 4/1/2017,10/1/2017
2, 12, 44, 8/1/2017, 10/1/2017
2, 15, 55, 11/1/2017, 10/1/2017
];
Temp1:
NoConcatenate LOAD *
Resident Temp
Where Date > CD and Date < Previous(CD)
Order By Rowno desc;
Drop Table Temp;
Regards,
Antonio
You need this in the script or front end?
I need this in the script itself. Need to join certain tables after that.
You may try this script:
tmp_table1:
LOAD * INLINE [
GR, RT, QID, CD, Date
1, 8, 11, 6/1/2017, 8/1/2017
1, 10, 22, 9/1/2017, 8/1/2017
1, 15, 33, 15/1/2017, 8/1/2017
2, 9, 66, 4/1/2017,10/1/2017
2, 12, 44, 8/1/2017, 10/1/2017
2, 15, 55, 11/1/2017, 10/1/2017
];
tmp_table2:
load
GR, RT, QID, CD, Date, Peek(CD,RowNo(),'tmp_table1') as prevCD
Resident tmp_table1;
NoConcatenate
table1:
load *
Resident tmp_table2
where Date>CD and Date<prevCD
;
DROP Tables tmp_table1, tmp_table2;
Thanks for the reply! But the solution is not what i want.
You are welcome, but please explain why this is not what you want, maybe I didn't understand your requirement correctly.
Hi Arpit,
maybe this
Temp:
LOAD *,RowNo() as Rowno INLINE [
GR, RT, QID, CD, Date
1, 8, 11, 6/1/2017, 8/1/2017
1, 10, 22, 9/1/2017, 8/1/2017
1, 15, 33, 15/1/2017, 8/1/2017
2, 9, 66, 4/1/2017,10/1/2017
2, 12, 44, 8/1/2017, 10/1/2017
2, 15, 55, 11/1/2017, 10/1/2017
];
Temp1:
NoConcatenate LOAD *
Resident Temp
Where Date > CD and Date < Previous(CD)
Order By Rowno desc;
Drop Table Temp;
Regards,
Antonio
Try like:
Thanks Antonio for the reply. But i dont know why im getting only one row as output
Any reason for this?
im running the same script.
I should have refreshed the page before posting.