Joining two columns

[yoganantha321]

hi guys,

I have two tables as two qvd as data sources.

table 1: (EXT_presentEmpInfo.qvd)

LOAD EmployeeID,

FullName,

  Date(Date,'MM/DD/YYYY') as Date

FROM

(qvd);

table 2: (EXT_ResourceTimesheet.qvd)

LOAD EmployeeID,

  sheet_date,

  worked_hours

FROM

(qvd);

I have Date in table 1, it contains the all the dates of an employee from the joining till today. In table 2, sheet_date has all the dates of an employee who has worked alone from the joining till today.

I need a table exactly as shown in below table by combining these two tables,

I have tried below codes but not worked,

Present:

LOAD EmployeeID,

     Date(Date,'MM/DD/YYYY')as Date

FROM

(qvd);

left join

Timesheet:

LOAD EmployeeID,

     Date(sheet_date,'MM/DD/YYYY') as Date,

     1 as Flag

FROM

(qvd);

My output for this code is as shown below,

I have also attached my real data along with this. Please help.

Regards,

Yoganantha Prakash G P and Bose

[Anonymous · Accepted solution]

Hello Friend,

Below is answer on your problem:

Map_Resource:

Mapping LOAD

    EmployeeID & '-' &date(sheet_date,'YYYY-MM-DD') as Key_DateEmp,

      '1' as flag

FROM EXT_ResourceTimesheet.qvd  (qvd);

EMP:

LOAD EmployeeID,

    FullName,

    Date,

    ApplyMap('Map_Resource', EmployeeID & '-' & Date(Date),0) as FLAG

FROM EXT_presentEmpInfo.qvd (qvd);

Regards

Jacek.

[yoganantha321]

thanks  Jacek

This is what i expected output

[praveen_prithiviraj]

Hi Yoganantha,

Please try the below script...

Map_Resource:

Mapping LOAD

    EmployeeID & '-' &date(sheet_date,'YYYY-MM-DD') as Key_DateEmp,

      '1' as flag

FROM EXT_ResourceTimesheet.qvd  (qvd);

EMP:

LOAD EmployeeID,

          FullName,

          Date,

           ApplyMap('Map_Resource', EmployeeID & '-' & Date(Date,'YYYY-MM-DD'),0) as FLAG

FROM EXT_presentEmpInfo.qvd (qvd);

Hi Jacek,

Thanks for sharing..

we need to do small correction, the date which we have is "MM-DD-YYYY HH:MM:SS" format.

Due to this dates are not matching with two tables.

so we can use apply map with Date(Date,'YYYY-MM-DD').

Thank You,

Praveen Prithiviraj