Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have following qvd (qvd1)
SEC_ID | ELEMENT_ID | NAME | PAID_UP_CAPITAL | TYPE | LAST_ACCESSED | DATA_CHANGED_DATE |
PK0000101078 | 54545 | MAM106 | 5500000 | EQUITY | 4/9/2156 2:46:09 PM | |
PK0000101078 | 54545 | MAM106 | 6500000 | EQUITY | 4/9/2156 3:17:49 PM | |
PK0000101078 | 54545 | MAM106 | 7500000 | EQUITY | 12/18/2161 6:58:36 PM | |
PK0000101078 | 54545 | MAM106 | 8500000 | EQUITY | 12/1/2163 9:28:22 AM | |
PK0000101078 | 54545 | MAM106 | 5500000000000 | EQUITY | 8/17/2186 10:39:46 AM | |
PK0000000114 | 03335 | SOFT | 98000000 | EQUITY | 9/23/2004 10:14:22 PM | |
PK0000101037 | 54545 | MAM103 | 465306306 | EQUITY | 12/11/2182 11:31:20 AM | |
PK0000101037 | 54545 | MAM103 | 465311306 | EQUITY | 12/12/2182 12:08:43 PM | |
PK0000101037 | 54545 | MAM103 | 465311306 | EQUITY | 12/12/2182 3:27:39 PM | |
PK0000101037 | 54545 | MAM103 | 1000000000 | EQUITY | 12/14/2182 10:43:23 AM | |
PK0000101037 | 54545 | MAM103 | 1000000000 | EQUITY | 6/23/2183 2:20:46 PM | |
PK0000101037 | 54545 | MAM103 | 1000000000 | EQUITY | 4/11/2188 10:31:19 AM | |
PK0000101037 | 54545 | MAM103 | 516153142 | EQUITY | 10/16/2188 1:57:11 PM |
and another qvd (qvd2)
Sec ID_RTA | ELEMENT_ID_RTA | Balance | TRANSACTION_DATE |
PK0000000114 | 03335 | 20 | 9/18/2004 4:37:01 AM |
PK0000000114 | 03335 | 30 | 9/19/2004 10:12:15 AM |
PK0000000114 | 03335 | 10000 | 9/20/2004 3:36:42 AM |
PK0000000114 | 03335 | 100 | 9/23/2004 10:14:22 PM |
PK0000101078 | 54545 | 5010 | 2/10/2184 12:00:00 AM |
PK0000101078 | 54545 | 5020 | 7/30/2183 12:00:00 AM |
PK0000101078 | 54545 | 5030 | 8/17/2186 12:00:00 AM |
PK0000101078 | 54541 | 1001 | 8/12/2183 12:00:00 AM |
PK0000101078 | 54541 | 1002 | 12/22/2183 12:00:00 AM |
PK0000101037 | 54545 | 511042163 | 7/17/2186 12:00:00 AM |
PK0000101037 | 54545 | 511042663 | 8/11/2186 12:00:00 AM |
PK0000101037 | 54545 | 511042663 | 10/12/2188 12:00:00 AM |
PK0000101037 | 54545 | 511042663 | 10/13/2188 12:00:00 AM |
PK0000101037 | 54545 | 516153132 | 10/15/2188 12:00:00 AM |
PK0000101037 | 54545 | 516153142 | 10/16/2188 12:00:00 AM |
PK0000101037 | 54545 | 516153142 | 8/4/2210 12:00:00 AM |
and AS on date:
I used formula In QVD1: =if(LAST_ACCESSED <= Till_Date, SEC_ID, if(DATA_CHANGED_DATE <= Till_Date, SEC_ID,Null()))
Please note that Till_Date is a variable of As on Date.
I used formula In QVD2: =if(SEC_ID = SEC_ID_RTA AND ELEMENT_ID = ELEMENT_ID_RTA AND TRANSACTION_DATE <= Till_Date, SHARES_AVAILABLE, Null())
Actually,qvd1 check last sorted LAST_ACCESSED date is available otherwise check last sorted DATA_CHANGED_DATE date record
and then qvd1 sec_ID and ELEMENT_ID pass qvd2 and check last sorted TRANSACTION_DATE balance in the qvd2. (e.g., balance of max(TRANSACTION_DATE ) <= Till_Date )
here is the desire result.
SEC_ID | ELEMENT_ID_RTA | NAME | PAID_UP_CAPITAL | TYPE | LAST_ACCESSED | DATA_CHANGED_DATE | Balance | TRANSACTION_DATE |
PK0000101078 | 54545 | MAM106 | 5500000000000 | EQUITY | 8/17/2186 10:39:46 AM | 1002 | 12/22/2183 12:00:00 AM | |
PK0000000114 | 03335 | SOFT | 98000000 | EQUITY | 9/23/2004 10:14:22 PM | 100 | 9/23/2004 10:14:22 PM | |
PK0000101037 | 54545 | MAM103 | 516153142 | EQUITY | 10/16/2188 1:57:11 PM | 516153142 | 8/4/2210 12:00:00 AM |
If As on Date = 5/12/2184
SEC_ID | ELEMENT_ID_RTA | NAME | PAID_UP_CAPITAL | TYPE | LAST_ACCESSED | DATA_CHANGED_DATE | Balance | TRANSACTION_DATE |
PK0000101078 | 54545 | MAM106 | 8500000 | EQUITY | 12/1/2163 9:28:22 AM | 1002 | 12/22/2183 12:00:00 AM | |
PK0000000114 | 03335 | SOFT | 98000000 | EQUITY | 9/23/2004 10:14:22 PM | 100 | 9/23/2004 10:14:22 PM | |
PK0000101037 | 54545 | MAM103 | 1000000000 | EQUITY | 6/23/2183 2:20:46 PM | 0 |
Lets make it simple.
Please tell about how to map the two different dates column QVDS with same id at run time date selection in one grid.
QVD1:
SEC_ID | ELEMENT_ID | NAME | PAID_UP_CAPITAL | TYPE | LAST_ACCESSED | DATA_CHANGED_DATE |
PK0000101078 | 54545 | MAM106 | 5500000 | EQUITY | 4/9/2156 2:46:09 PM | |
PK0000101078 | 54545 | MAM106 | 6500000 | EQUITY | 4/9/2156 3:17:49 PM | |
PK0000101078 | 54545 | MAM106 | 7500000 | EQUITY | 12/18/2161 6:58:36 PM | |
PK0000101078 | 54545 | MAM106 | 8500000 | EQUITY | 12/1/2163 9:28:22 AM | |
PK0000101078 | 54545 | MAM106 | 5500000000000 | EQUITY | 8/17/2186 10:39:46 AM |
QVD2:
Sec ID_RTA | ELEMENT_ID_RTA | Balance | TRANSACTION_DATE |
PK0000101078 | 54545 | 5010 | 2/10/2184 0:00 |
PK0000101078 | 54545 | 5020 | 7/30/2183 0:00 |
PK0000101078 | 54545 | 5030 | 8/17/2186 0:00 |
PK0000101078 | 54541 | 1001 | 8/12/2183 0:00 |
PK0000101078 | 54541 | 1002 | 12/22/2183 0:00 |
here is the desire result.
If select the Date = 5/12/2184
SEC_ID | ELEMENT_ID_RTA | NAME | PAID_UP_CAPITAL | TYPE | LAST_ACCESSED | DATA_CHANGED_DATE | Balance | TRANSACTION_DATE |
PK0000101078 | 54545 | MAM106 | 8500000 | EQUITY | 12/1/2163 9:28:22 AM | 1002 | 12/22/2183 12:00:00 |
check last sorted date(TRANSACTION_DATE) balance in the qvd2. (e.g., balance of max(TRANSACTION_DATE) <= selected_date)
and
if check last sorted date(LAST_ACCESSED) paid up capital in the qvd1. (e.g., balance of LAST_ACCESSED) < selected_date)
else check last sorted date(DATA_CHANGED_DATE) paid up capital in the qvd1. (e.g., balance of DATA_CHANGED_DATE) < selected_date)