Concatenating two tables changes the values in a field in the second table

[QS_]

I have two tables YearlyCalculation and Eightweeks that I need to concatenate. 

This my script for loading the YearlyCalculation table.

YearlyCalculation:
Load flt_no,
dep_stn,
arr_stn,
Month(flt_date) as Day_Month,
Year(flt_date) as Period,
((taxi_out_fuel_weight_kg+trip_fuel_kg) - (fob_engine_start_kg-fob_intime_kg))/30 as Delta_Flight_Fuel,
(interval(ata-atd-(sta-std),'m')) as Actual_Flight_Time_Over_Plan,

((zfw*2.971548)-planned_zero_fuel_weight_kg)/30 as ZFW_drop
Resident LidoData

The table looks like so(Some fields not shown). For each flt_no, it shows me the Avg(Delta_Flight_Fuel) for the past 13 months.

flt_no	arr_stn	Day_Month	Period	Avg(Delta_Flight_Fuel)
1	LHR	Jan	2023	-38.61364516
1	LHR	Feb	2023	-31.27633333
1	LHR	Mar	2023	-44.42931111
1	LHR	Apr	2023	-44.76470115
1	LHR	May	2023	-37.98098925
1	LHR	Jun	2023	-54.72424138
1	LHR	Jul	2023	-50.75052222
1	LHR	Aug	2023	-37.28553763
1	LHR	Sep	2022	-28.97011494
1	LHR	Sep	2023	-38.30317333
1	LHR	Oct	2022	-33.4694881
1	LHR	Oct	2023	-66.6855
1	LHR	Nov	2022	-34.19263333
1	LHR	Dec	2022	-48.894

This is my script for loading the eightweeks table

Eightweeks:
Load flt_no as eight_flt_no,
dep_stn as eight_dep_stn,
arr_stn as eight_arr_stn,
WeekDay(flt_date) as eight_Day_Month,
if(Week(Today()-8), 'Last 8') as eight_Period,
((taxi_out_fuel_weight_kg+trip_fuel_kg) - (fob_engine_start_kg-fob_intime_kg))/30 as eight_Delta_Flight_Fuel,
(interval(ata-atd-(sta-std),'m')) as eight_Actual_Flight_Time_Over_Plan,
((zfw*2.971548)-planned_zero_fuel_weight_kg)/30 as eight_ZFW_drop
Resident LidoData;

The table looks like so. For each flt_no, it shows me the Avg(Delta_Flight_Fuel) for every day of the week from 8 weeks ago.

eight_flt_no	eight_arr_stn	eight_Day_Month	eight_Period	Avg(eight_Delta_Flight_Fuel)
1	LHR	Sun	Last 8	-42.62955758
1	LHR	Mon	Last 8	-52.98312727
1	LHR	Tue	Last 8	-37.15182143
1	LHR	Wed	Last 8	-41.85949091
1	LHR	Thu	Last 8	-35.53621053
1	LHR	Fri	Last 8	-36.11191954
1	LHR	Sat	Last 8	-39.64489103

I tried concatenating the two tables using the script below

Concatenate(YearlyCalculation)
Load
eight_flt_no as flt_no,
eight_dep_stn as dep_stn,
eight_arr_stn as arr_stn,
eight_Day_Month as Day_Month,
eight_Period as Period,
eight_Delta_Flight_Fuel as Delta_Flight_Fuel,
eight_Actual_Flight_Time_Over_Plan as Actual_Flight_Time_Over_Plan,
eight_ZFW_drop as ZFW_drop
Resident Eightweeks;

The tables are concatenated but the Day_Month values coming from the Eightweeks table is no longer correct. 

1	LHR	Sun	Last 8	-42.62955758
1	LHR	Jan	Last 8	-52.98312727
1	LHR	Jan	2023	-38.61364516
1	LHR	Feb	Last 8	-37.15182143
1	LHR	Feb	2023	-31.27633333
1	LHR	Mar	Last 8	-41.85949091
1	LHR	Mar	2023	-44.42931111
1	LHR	Apr	Last 8	-35.53621053
1	LHR	Apr	2023	-44.76470115
1	LHR	May	Last 8	-36.11191954
1	LHR	May	2023	-37.98098925
1	LHR	Jun	Last 8	-39.64489103
1	LHR	Jun	2023	-54.72424138

It seems like except for Sunday the rest of the WeekNames change to the month name

flt_no	arr_stn	Day_Month	Period	Avg(Delta_Flight_Fuel)
1	LHR	Sun	Last 8	-42.62955758
1	LHR	Jan	Last 8	-52.98312727
1	LHR	Feb	Last 8	-37.15182143
1	LHR	Mar	Last 8	-41.85949091
1	LHR	Apr	Last 8	-35.53621053
1	LHR	May	Last 8	-36.11191954
1	LHR	Jun	Last 8	-39.64489103

How can I fix this?

[L_Hop]

Hi QS_,

I think you should recalculate your Day_Month in Eightweeks table. It seems your Eightweeks date field not in proper format.

If you have date field like "eight_flt_date", you may  use "WeekDay(eight_flt_date) as eight_Day_Month".

 regards.

[marcus_sommer]

month() and weekday() results in dual() values whereby the numeric parts are the leading ones and each numeric value could have only one string-representation - which in your scenario results in an overlapping. You could simply avoid it by wrapping the functions with text() like:

text(month())
text(weekday())

[QS_ · Accepted solution]

I think this could be the possible reason for the overlap. I used MonthName(flt_date) as Day_Month instead of Month(flt_date) as Day_Month and then did the Concatenation. This seems to have fixed the issue with the overlapping.

flt_no	arr_stn	Day_Month	Period	Avg(Delta_Flight_Fuel)
1	LHR	Sun	Last 8	-43.21517262
1	LHR	Mon	Last 8	-52.98312727
1	LHR	Tue	Last 8	-37.15182143
1	LHR	Wed	Last 8	-41.85949091
1	LHR	Thu	Last 8	-35.53621053
1	LHR	Fri	Last 8	-36.11191954
1	LHR	Sat	Last 8	-39.71769182
1	LHR	Sep 2022	2022	-28.97011494
1	LHR	Oct 2022	2022	-33.4694881
1	LHR	Nov 2022	2022	-34.19263333
1	LHR	Dec 2022	2022	-48.894
1	LHR	Jan 2023	2023	-38.61364516
1	LHR	Feb 2023	2023	-31.27633333
1	LHR	Mar 2023	2023	-44.42931111
1	LHR	Apr 2023	2023	-44.76470115
1	LHR	May 2023	2023	-37.98098925

On the chart I calculate the dimension as PurgeChar(Day_Month,'0123456789') and see the values, exactly as I would like.