Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 tables as below, I can't create a common Year / Week column as that creates a circular reference.
The plan table contains all possible Year / Week combinations that may appear in the actuals table. I want to plot plan vs actuals on a chart to show where these don't match up as in the excel chart below. What's the best way to do this without causing a circular reference?
Table 1 Plan
Year / Week | Project Code | Type | Total |
2024/01 | 123 | Build | 1 |
2024/01 | 456 | Design | 5 |
2024/02 | 123 | Design | 3 |
2024/03 | 123 | Build | 0.5 |
2024/04 | |||
2024/05 | 456 | Test | 5 |
Table 2 Actuals
Year / Week | Project Code | Type | Total |
2024/01 | 456 | Build | 1 |
2024/01 | 123 | Design | 5 |
2024/03 | 123 | Build | 0.5 |
2024/04 | |||
2024/05 | 456 | Design | 5 |
Hello, wouldn't concatenating the two tables but adding a TAG to differentiate between Plan and Actuals solve your problem? Then, simply use the set analysis by choosing the TAG corresponding to your measure.
I would but there are already a lot of joins in the app that I don't want to mess up! The tables do look to be concatenated already as below.
The YYYYWW field in table 1 is the master list of all the YYYYWW that I want to plot the plan and actuals against.
Actuals:
LOAD
Project,
Nmhrs,
OThrs,
(Nmhrs)+(OThrs) as Actual_Hours,
ProjectID,
TdateYear &'/' &num(TdateWeek, '00') as TYearWeek,
Type
FROM [Table2 file]
Concatenate
LOAD
Project_ID as ProjectID,
Dep_ID as Type
FROM [Table3 file]
Concatenate
LOAD
ProjectNo as ProjectID,
"Resource Type" as Type
" YYYYWW" as TYearWeek,
FROM [Table1]
I don't know what you're trying to do but is it normal that you use concatenates rather than joins here?
I could imagine that you get better results by joining at first table1 to table3 and then concatenating it to table2.
Beside this the fear of mess something indicates that there are already too many load-statements within the application - not mainly to any technically and/or performance aspects else to the efforts of developing/maintaining and keeping a sufficient control and overview. Therefore distributing the job to several applications and/or layer might be helpful.