Need some help in compute similar to lookup using Aggr and guess alternate state

[gopal5879]

Hello folks need some help desperately. I am trying to figure out using set analysis.I want to compute   total adp days worked , expression ADP DAYS WORKED on chart Summary  for each of the workhist months .

Expression ADP_WORK_DAYS is computed by look up of column ADP_MMYYYY in chart Summary .  There is no relation as model 

between ADPDATA table  and WORKHIST table .

Can you someone guide me in doing this. I have posted sample output in chart Summary.

I am trying to do something like this with no success.
=SUM( AGGR( MAX ( {< WKHIS_MMYYYY{"=ADP_MMYYYY"}>} ADP_WORK_DAYS),ADP_WORK_DATE,ADP_MMYYYY" ))

		ADPDATA table
		EMP	ADP_WORK_DATE	ADP_MMYYYYY	ADP_WORK_DAYS
		SCOTT	6/1/2023	62023	0.5
		SCOTT	6/2/2023	62023	0.5
		SCOTT	6/5/2023	62023	0.25
		SCOTT	6/6/2023	62023	0.25
		WORKHIST table
		WKHIST DAY	WKHIS_MMYYYY	WKHIST_USER
		6/1/2023	62023	SCOTT
		6/2/2023	62023	SCOTT
		6/5/2023	62023	MIKE
		6/6/2023	62023	MIKE
		Chart Summary
		Chart	WKHIS_MMYYYY	ADP DAYS WORKED
		SCOTT	62023	1
		MIKE	62023	0.5

[BrunPierre]

Describe how you arrived at the output table.

[gopal5879]

Hello Brun,

Summary is  straight table chart with dimensions WKHIS_MMYYYY , WKHIST_USER from WORKHIST table 

and ADP DAYS WORKED is a measure.

I need help in computing ADP DAYS WORKED which calculated from ADPDATA table by matching WKHIS_MMYYYY and Emp against 

 ADPDATA table for corresponding EMP Scott /Mike and ADP_MMYYYYY  ( month & yyyy) field values.

Example for the month of June, 2023 ( 62023) , sum of ADP_WORK_DAYS is 0.5 +0.5 =1

[BrunPierre]

Hi, You will need to create a link between the two tables using the dates as below.

ADPDATA:
LOAD EMP,
ADP_WORK_DATE,
ADP_MMYYYYY,
ADP_WORK_DAYS,
Date(ADP_WORK_DATE, 'MM/DD/YYYY') as %Date

FROM ABC;

WORKHIST:
LOAD "WKHIST DAY",
WKHIS_MMYYYY,
WKHIST_USER
Date("WKHIST DAY", 'MM/DD/YYYY') as %Date

FROM XYZ;

[marcus_sommer · Accepted solution]

It looked as if both tables contain the same information - like one the current data and the other historic ones respectively any kind of budget data. If so both tables could - ans should - be concatenated by harmonizing the field-names and data-structures  and adding the extra-information about the source-table with which the data could be differentiated again in dimensions/selections and/or conditions.