Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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

Hello, 

I have following qvd (qvd1)

SEC_IDELEMENT_IDNAMEPAID_UP_CAPITALTYPELAST_ACCESSEDDATA_CHANGED_DATE
PK000010107854545MAM1065500000EQUITY 4/9/2156 2:46:09 PM
PK000010107854545MAM1066500000EQUITY 4/9/2156 3:17:49 PM
PK000010107854545MAM1067500000EQUITY 12/18/2161 6:58:36 PM
PK000010107854545MAM1068500000EQUITY 12/1/2163 9:28:22 AM
PK000010107854545MAM1065500000000000EQUITY8/17/2186 10:39:46 AM 
PK000000011403335SOFT98000000EQUITY9/23/2004 10:14:22 PM 
PK000010103754545MAM103465306306EQUITY 12/11/2182 11:31:20 AM
PK000010103754545MAM103465311306EQUITY 12/12/2182 12:08:43 PM
PK000010103754545MAM103465311306EQUITY 12/12/2182 3:27:39 PM
PK000010103754545MAM1031000000000EQUITY 12/14/2182 10:43:23 AM
PK000010103754545MAM1031000000000EQUITY 6/23/2183 2:20:46 PM
PK000010103754545MAM1031000000000EQUITY 4/11/2188 10:31:19 AM
PK000010103754545MAM103516153142EQUITY10/16/2188 1:57:11 PM 

 

and another qvd (qvd2)

Sec ID_RTAELEMENT_ID_RTABalanceTRANSACTION_DATE
PK000000011403335209/18/2004 4:37:01 AM
PK000000011403335309/19/2004 10:12:15 AM
PK000000011403335100009/20/2004 3:36:42 AM
PK0000000114033351009/23/2004 10:14:22 PM
PK00001010785454550102/10/2184 12:00:00 AM
PK00001010785454550207/30/2183 12:00:00 AM
PK00001010785454550308/17/2186 12:00:00 AM
PK00001010785454110018/12/2183 12:00:00 AM
PK000010107854541100212/22/2183 12:00:00 AM
PK0000101037545455110421637/17/2186 12:00:00 AM
PK0000101037545455110426638/11/2186 12:00:00 AM
PK00001010375454551104266310/12/2188 12:00:00 AM
PK00001010375454551104266310/13/2188 12:00:00 AM
PK00001010375454551615313210/15/2188 12:00:00 AM
PK00001010375454551615314210/16/2188 12:00:00 AM
PK0000101037545455161531428/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_IDELEMENT_ID_RTANAMEPAID_UP_CAPITALTYPELAST_ACCESSEDDATA_CHANGED_DATEBalanceTRANSACTION_DATE
PK000010107854545MAM1065500000000000EQUITY8/17/2186 10:39:46 AM 100212/22/2183 12:00:00 AM
PK000000011403335SOFT98000000EQUITY9/23/2004 10:14:22 PM 1009/23/2004 10:14:22 PM
PK000010103754545MAM103516153142EQUITY10/16/2188 1:57:11 PM 5161531428/4/2210 12:00:00 AM

 

 

If As on Date = 5/12/2184

SEC_IDELEMENT_ID_RTANAMEPAID_UP_CAPITALTYPELAST_ACCESSEDDATA_CHANGED_DATEBalanceTRANSACTION_DATE
PK000010107854545MAM1068500000EQUITY 12/1/2163 9:28:22 AM100212/22/2183 12:00:00 AM
PK000000011403335SOFT98000000EQUITY9/23/2004 10:14:22 PM 1009/23/2004 10:14:22 PM
PK000010103754545MAM1031000000000EQUITY 6/23/2183 2:20:46 PM0 

 

 

 

 

 

 

1 Reply
Highlighted
Contributor III
Contributor III

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_IDELEMENT_IDNAMEPAID_UP_CAPITALTYPELAST_ACCESSEDDATA_CHANGED_DATE
PK000010107854545MAM1065500000EQUITY 4/9/2156 2:46:09 PM
PK000010107854545MAM1066500000EQUITY 4/9/2156 3:17:49 PM
PK000010107854545MAM1067500000EQUITY 12/18/2161 6:58:36 PM
PK000010107854545MAM1068500000EQUITY 12/1/2163 9:28:22 AM
PK000010107854545MAM1065500000000000EQUITY8/17/2186 10:39:46 AM 

 

QVD2:

Sec ID_RTAELEMENT_ID_RTABalanceTRANSACTION_DATE
PK00001010785454550102/10/2184 0:00
PK00001010785454550207/30/2183 0:00
PK00001010785454550308/17/2186 0:00
PK00001010785454110018/12/2183 0:00
PK000010107854541100212/22/2183 0:00

 

here is the desire result.

If select the Date = 5/12/2184

SEC_IDELEMENT_ID_RTANAMEPAID_UP_CAPITALTYPELAST_ACCESSEDDATA_CHANGED_DATEBalanceTRANSACTION_DATE
PK000010107854545MAM1068500000EQUITY 12/1/2163 9:28:22 AM100212/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)