Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please suggest the following requirement.
I have following qvd (qvd1) with 4 different dates.
ID | F_NUMBER | NAV_1 |
2011 | 5 | 9/3/2032 |
2011 | 5 | 10/3/2032 |
2011 | 5 | 11/3/2032 |
2011 | 5 | 12/3/2032 |
and another qvd (qvd2) with 3 different dates and balance.
ID | F_NUMBER | NAV_2 | BALANCE |
2011 | 5 | 8/3/2032 | 2400 |
2011 | 5 | 9/3/2032 | 2500 |
2011 | 5 | 12/3/2032 | 2511 |
qvd1 check last sorted date balance in the qvd2. (e.g., balance of max(NAV_2) <= NAV_1)
here is the desire result.
ID | F_NUMBER | NAV | BALANCE |
2011 | 5 | 9/3/2032 | 2500 |
2011 | 5 | 10/3/2032 | 2500 |
2011 | 5 | 11/3/2032 | 2500 |
2011 | 5 | 12/3/2032 | 2511 |
May be this
Table1:
LOAD * INLINE [
ID, F_NUMBER, NAV_2, BALANCE
2011, 5, 8/3/2032, 2400
2011, 5, 9/3/2032, 2500
2011, 5, 12/3/2032, 2511
];
Table2:
LOAD *,
Date(If(ID = Previous(ID) and F_NUMBER = Previous(F_NUMBER), Peek('NAV_2')-1, '12/31/9999')) as NAV_2_End
Resident Table1
Order By ID, F_NUMBER, NAV_2 desc;
DROP Table Table1;
Table3:
LOAD * INLINE [
ID, F_NUMBER, NAV_1
2011, 5, 9/3/2032
2011, 5, 10/3/2032
2011, 5, 11/3/2032
2011, 5, 12/3/2032
];
Inner Join (Table2)
IntervalMatch(NAV_1, ID, F_NUMBER)
LOAD NAV_2,
NAV_2_End,
ID,
F_NUMBER
Resident Table2;
Inner Join (Table2)
LOAD *
Resident Table3;
DROP Table Table3;
May be this
Table1:
LOAD * INLINE [
ID, F_NUMBER, NAV_2, BALANCE
2011, 5, 8/3/2032, 2400
2011, 5, 9/3/2032, 2500
2011, 5, 12/3/2032, 2511
];
Table2:
LOAD *,
Date(If(ID = Previous(ID) and F_NUMBER = Previous(F_NUMBER), Peek('NAV_2')-1, '12/31/9999')) as NAV_2_End
Resident Table1
Order By ID, F_NUMBER, NAV_2 desc;
DROP Table Table1;
Table3:
LOAD * INLINE [
ID, F_NUMBER, NAV_1
2011, 5, 9/3/2032
2011, 5, 10/3/2032
2011, 5, 11/3/2032
2011, 5, 12/3/2032
];
Inner Join (Table2)
IntervalMatch(NAV_1, ID, F_NUMBER)
LOAD NAV_2,
NAV_2_End,
ID,
F_NUMBER
Resident Table2;
Inner Join (Table2)
LOAD *
Resident Table3;
DROP Table Table3;
Thanks for your kind reply.
you are requested to please reply the following also.