Load Master Records for Fact Data.

[#1]

Hi,

I have Transaction Table(employee leaves) and a Master table(employee master).

I want to load employee master for which employee leave exists.

Can someone give me syntax and code for this.

Regards, Nitin

[phaneendra_kunc · Accepted solution]

Load the Transaction Table first and then load your Employee Master table with WHERE EXIST command.

Ex:

Emploee_Transaction:

Load Employee_ID, ..., .... From Emploee_Transaction.qvd;

Emploee_Master:

Employee_ID, NAME, CITY,etc... From Employee_Master

where exists (Employee_ID);

Assuming you have a key with similar name in both the tables (Employee_ID)

[#3]

Thanks for the answer, it solved my problem now I have another complexity in exists clause.

Can I put two fields in exists clause? e.g.   where exists(EmpType,Employee_ID)

Scenario: Emp-Master 1 - Permanent Employees - EmpType = 'P'

                Emp-Master 2 - Subcontract Employees - EmpType = 'S'

My Transaction table will be EmpTyp and EmpID

[phaneendra_kunc]

You can try these

where exists(Employee_ID) and exists(EmpType)

or you can create a Key like EmpType&'-'&Employee_ID as EMP_KEY in your transaction table load and use below where clause.

where exists(EmpType&'-'&Employee_ID)

if your data is not that big you can use LEFT KEEP instead of EXISTS.

Ex:

Emploee_Transaction:

Load Employee_ID, ..., .... From Emploee_Transaction.qvd;

Emploee_Master:

left keep (Emploee_Transaction)

Employee_ID, NAME, CITY,etc... From Employee_Master;

[#5]

Thanks for reply,

I tried both option but it is not working.

LOAD
    TRN_DATE,
    EMP_TYPE&'-'&EMP_ID as Key    //Composite key
    FROM [lib://Trn\EMPTRN.QVD]
(qvd)
WHERE NUM(FLOOR(TRN_DATE))>=NUM(FLOOR(MAKEDATE(2016,10,1)));

EMPMST:
LOAD
    SUBCON_CD,
    SUBCON_NAME,
    EMP_RANK,
    EMP_TYPE&'-'&EMP_ID as Key    //Composite key
FROM [lib://Mst\EMPMST.QVD]
(qvd)
where exists(EMP_ID) and exists(EMP_TYPE);

Here is the code.

[phaneendra_kunc]

Try this.

EMPTRN:

LOAD

TRN_DATE,

EMP_TYPE&'-'&EMP_ID as Key //Composite key

FROM

(qvd)

WHERE NUM(FLOOR(TRN_DATE))>=NUM(FLOOR(MAKEDATE(2016,10,1)));

EMPMST:

LOAD

SUBCON_CD,

SUBCON_NAME,

EMP_RANK,

EMP_TYPE&'-'&EMP_ID as Key //Composite key

FROM

(qvd)

where exists(Key, EMP_TYPE&'-'&EMP_ID);

//====================== Second option ================================

EMPTRN:

LOAD

TRN_DATE,

EMP_TYPE&'-'&EMP_ID as Key //Composite key

FROM

(qvd)

WHERE NUM(FLOOR(TRN_DATE))>=NUM(FLOOR(MAKEDATE(2016,10,1)));

EMPMST:

Left Keep (EMPTRN)

LOAD

SUBCON_CD,

SUBCON_NAME,

EMP_RANK,

EMP_TYPE&'-'&EMP_ID as Key //Composite key

FROM

(qvd)

where;

[#7]

Thanks, It worked.