Using a Join with 2 tables results in table changing name

alespooletto — Tue, 23 Jan 2024 13:26:20 GMT

Hello, I would like to perform a join on the following tables:

LABOR_LOG_MEMBER: LOAD LABOR_LOG_KEY, Subfield(SFC_KEY,',',2) as SFC, Subfield(LABOR_LOG_RESOURCE,',',2) as Resource, LABOR_LOG_OPERATION FROM LABOR_LOG_MEMBER.qvd where Wildmatch(LABOR_LOG_RESOURCE,'*CELL*'); LABOR_LOG: Inner Keep (LABOR_LOG_MEMBER) LOAD LABOR_LOG_KEY, Subfield(LABOR,',',2) as User, LABOR_LOG_ACTIVE, Time(LABOR_LOG_START) as LABOR_LOG_START, Time(LABOR_LOG_END) as LABOR_LOG_END, Date(Floor(LABOR_LOG_END)) as Date, LABOR_LOG_DURATION, Time(LABOR_LOG_END - LABOR_LOG_START) as Duration FROM LABOR_LOG.qvd where Date(Floor(LABOR_LOG_END))>= addmonths(today(),-24) and WildMatch(LABOR,'*IT*') and Date(Floor(LABOR_LOG_END))<= today(); Join(LABOR_LOG) ActivityLog: LOAD ACTION_CODE, Date(Floor(ACTIVITY_LOG_DATE_TIME)) as Date, ConvertToLocalTime(Timestamp(ACTIVITY_LOG_DATE_TIME), 'Rome') as DateTime, ACTIVITY_LOG_USER as User, IF(Left(ACTIVITY_LOG_USER, 2) = 'IT', ACTIVITY_LOG_USER) as Employee, SubField(ACTIVITY_LOG_USER, 'EN', 1) as CommonPrefixUser, SFC, // Replace .01. and .02. with .00. Replace(SFC, SubField(SFC, '.', 2), '00') as OverallSFC, If(SubField(SFC,'.',2)='00','Main SFC','Sub-SFC') as MainSFC, ACTIVITY_LOG_OPERATION, SubField(ACTIVITY_LOG_OPERATION,'-',6) as Product, ACTIVITY_LOG_RESRCE as Resource, ACTIVITY_LOG_WORK_CENTER as WorkCenter FROM ACTIVITY_LOG.qvd WHERE Year(Date(Floor(ACTIVITY_LOG_DATE_TIME))) >= 2020 AND WildMatch(ACTION_CODE, 'START', 'COMPLETE', 'LABOR_ON', 'LABOR_OFF', 'REWORK'); ActionCodes: LOAD ACTION_CODE as Start_Complete Resident ActivityLog WHERE WildMatch(ACTION_CODE, 'START', 'COMPLETE', 'LABOR_ON', 'LABOR_OFF');

However, when I run this with debug it tells me this error:

Table 'ActivityLog' not found: ActionCodes: LOAD ACTION_CODE as Start_Complete Resident ActivityLog WHERE WildMatch(ACTION_CODE, 'START', 'COMPLETE', 'LABOR_ON', 'LABOR_OFF')

I don't understand why this isn't working, as the webpage document for using Join says I should write the join condition at the top, but then seemingly the tables change name. How can I fix this please? I want to join them into a single table, and then remove the other LOG_* tables, because they would just create synthetic keys.

Re: Using a Join with 2 tables results in table changing name

Or — Tue, 23 Jan 2024 14:18:45 GMT

Because you join "ActivityLog" to LABOR_LOG, no additional table is created. You'd simply need to load from Resident LABOR_LOG instead.

Note that that last load doesn't seem to actually do anything useful, so I'm not really sure what you're after. It'll just return a table with one field - Start_Complete - which will contain the strings 'START', 'COMPLETE', 'LABOR_ON', 'LABOR_OFF' as many times as they appear in the resident table. If you plan to use it as a filter for other expressions, you could just use an inline load instead.

topic Using a Join with 2 tables results in table changing name in App Development

Using a Join with 2 tables results in table changing name

Re: Using a Join with 2 tables results in table changing name