concat key based on left join into QVD

[cbaqir]

I need to create a concatenated key based on a field I did a left join to.

In the EPIC_CHECKBOOK the field is %CHECKBOOK_MONTH_MODULE. In the other table, I left joined to add the field WORK_NAME to the APP_QV_RCM.QVD. I need to get something like date(TRX_DATE,'MM/DD/YYYY')&'-'&WORK_NAME as %CHECKBOOK_MONTH_MODULE. Is it even possible?

SOMETHING LIKE:

LOAD *,date(TRX_DATE,'MM/DD/YYYY')&'-'&WORK_NAME as %CHECKBOOK_MONTH_MODULE
RESIDENT APP_QV_RCM;

EPIC_CHECKBOOK:
LOAD Division,
Module,
date(Month,'MM/DD/YYYY') as CHECKBOOK_MONTH,
date(Month,'MM/DD/YYYY')&'-'&Module as %CHECKBOOK_MONTH_MODULE,
Division&'-'&Module&'-'&date(Month,'YYYY/MM') as %DIV_MODULE_MONTH_KEY,
[Hours Type] as TASK_TYPE_OPT,
Division&' '&if (left([Hours Type], 😎 = 'National','',[Hours Type]) as %DIVISION_TASK_TYPE_KEY,
[Available Hrs.] as AVAIL_HOURS
FROM
[..\External_Data\Epic Checkbook Capacity 12.1.20.xlsx]
(ooxml, embedded labels, table is Checkbook);

APP_QV_RCM:
LOAD ITDW_ID,
APP,
TRX_DATE,
monthstart(date(TRX_DATE,'MM/DD/YYYY')) as CHECKBOOK_MONTH,
SEQ_ID,
// TASK_ID,
"SEQ_ID"&'-'&"TASK_ID" as %SEQ_TASK_ID_KEY,
STANDARD_ACTIVITY_NAME,
RESOURCE_CODE,
ORG_RESOURCE_CODE,
RESOURCE_SERVICE_LINE_COE_GROUP,
ORG_RESOURCE,
LABOR_CAT,
POSITION_STATE,
ALLOC_HRS,
ACT_HRS,
RESERVED_HRS,
REQUIRED_HRS,
REQUIREMENT_ID,
REQUIREMENT_NAME,
REQUIREMENT_SPECIALTY,
DAY_CAP,
AVAIL_CAP,
RES_TOTAL_CAP,
RESOURCE_TYPE,
DH_COMPANY_ID,
HOME_AU,
AU_NAME,
AU_IT_TOWER,
AU_PROG_PORT,
MANAGER_NAME,
COMPANY_NAME,
PV_ACT_CODE,
REQUEST_NUMBER,
RESP_APPROVER,
TIMESHEET_STATUS,
SIGN_DATE,
SIGN_DUE_DATE,
SIGN_COMP,
SIGNER_RESOURCE_CODE,
APPROVE_DATE,
APPROVE_DUE_DATE,
APPROVE_COMP,
APPROVER_RESOURCE_CODE,
CAP_HRS,
OP_HRS,
REQUIREMENT_LOCATION
// ETL_DATE

FROM
[..\QVD\01_Extract_QVD\APP_QV_RCM.qvd]
(qvd);

LEFT JOIN (APP_QV_RCM)
LOAD "SEQ_ID",

"WORK_NAME"
//Trim(Mid(WORK_NAME, Index(WORK_NAME, ' ') + 4))as MODULE

where wildmatch("WORK_NAME",'Epic OPT*');
SQL SELECT *
FROM "IT_DW".dbo."RPT_WORK";

[QFabian]

Hi @cbaqir ,  ofcourse you can create compose keys with more than one field to make good joins.

As i can't see your data, i only can suggest you a few changes to your concatenated fields :

num(monthstart(TRX_DATE)) & ' | ' & WORK_NAME as %_CHECKBOOK_MONTH_MODULE
num(monthstart(Month)) & ' | ' & Module as %_CHECKBOOK_MONTH_MODULE