how to map different table with different dates (runtime condition) in one grid

somacdc — Thu, 30 Jan 2020 13:12:20 GMT

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

Re: how to map different table with different dates (runtime condition) in one grid

somacdc — Fri, 07 Feb 2020 07:47:25 GMT

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)

topic Re: how to map different table with different dates (runtime condition) in one grid in QlikView

how to map different table with different dates (runtime condition) in one grid

Re: how to map different table with different dates (runtime condition) in one grid