topic Re: Joining two columns in QlikView

Joining two columns

yoganantha321 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Joining two columns

Anonymous — Thu, 25 May 2017 13:23:37 GMT

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.

Re: Joining two columns

yoganantha321 — Thu, 25 May 2017 13:52:57 GMT

thanks Jacek

This is what i expected output

Re: Joining two columns

praveen_prithiviraj — Thu, 25 May 2017 14:08:30 GMT

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