Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
yoganantha321
Creator II
Creator II

Joining two columns

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,

Screenshot_4.png

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,

Screenshot_62.png

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

Regards,

Yoganantha Prakash G P and Bose

1 Solution

Accepted Solutions
jaceknoden
Creator
Creator

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.

View solution in original post

3 Replies
jaceknoden
Creator
Creator

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
Creator II
Creator II
Author

thanks  Jacek

This is what i expected output

praveen_prithiviraj
Contributor III
Contributor III

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

Kind Regards,
Praveen Kumar. P