Hive - Using Lateral View Explode with Joined Table

Question

I am building some analysis and need to prep the date by joining two tables and then unpivot a date field and create one record for each "date_type". I have been trying to work with lateral view explode(array()) function but I can't figure out how to do this with columns from two separate tables. Any help would be appreciated, open to completely different methods.

TableA:

loan_number	app_date
123	07/09/2022
456	07/11/2022

TableB:

loan_number	funding_date	amount
123	08/13/2022	12000
456	08/18/2022	10000

Desired Result:

loan_number	date_type	date_value	amount
123	app_date	07/09/2022	12000
456	app_date	07/11/2022	10000
123	funding_date	08/13/2022	12000
456	funding_date	08/18/2022	10000

Here is some sample code related the example above I was trying to make work:

SELECT
    b.loan_number,
    b.amount,
    Date_Value
    
FROM TableA as a
    LEFT JOIN 
    TableB as b
        ON a.loan_number=b.loan_number
    LATERAL VIEW explode(array(to_date(a.app_date),to_date(b.funding_date)) Date_List AS Date_value

Answer 1

No need lateral view explode, just union, try below:

with base_data as (
select
    a.loan_number,
    a.app_date,
    b.funding_date,
    b.amount
from
    tableA a 
join
    tableB b on a.loan_number = b.loan_number
) 
select
    loan_number,
    'app_date'  as date_type,
    app_date    as date_value,
    amount
from
    base_data
union all
select
    loan_number,
    'funding_date'  as date_type,
    funding_date    as date_value,
    amount
from
    base_data