How can we create another field combining two fields?

[bhaveshp90]

Hello, 

I have created the below filter;

If(Cost_Code = '3', 'Mechanical Engineering',
If(Cost_Code = '4', 'Controls Engineering')) as Engineering_Area

 I am trying to combine both '3' and '4' to get the Total as another field in Engineering_Area. 

Can anyone please help me how to do this? I have attached the script in the qvd file. 

[vishsaggi]

Have not tried this script as we do not have qvds to run. Just try at your end and let us know if this works:

Project_Cost:
Load Project_Number &'-'& Region &'-'& if (match(Country_Name,'US','UK','Canada')>0, Country_Name, Capitalize(Country_Name)) &'-'& Snapshot_Date as Project_Cost_Key,
Year(Snapshot_Date) as Project_Cost_Year,
Month(Snapshot_Date) as Project_Cost_Month,
Project_Number as Project_Number_Project_Cost,
Cost_Code,
Updated_OEC_Budget,
Forecast_Cost_to_Complete,
Snapshot_Date as Project_Cost_Snapshot_Date
FROM
C:\Bhavesh\Qvd Files\Project_Cost.qvd
(qvd)
where Cost_Code = '3' or Cost_Code = '4';

CONCATENATE
LOAD Project_cost_snapshot_Date,
Project_Cost_key,
Sum(Cost_code) AS Cost_Code
Resident Project_Cost
Group By Project_cost_snapshot_Date,
Project_Cost_key;

Project_Cost1:
NoConcatenate Load
*,
If(Project_Cost_Month = 'Jan',1,
If(Project_Cost_Month = 'Feb',2,
If(Project_Cost_Month = 'Mar',3,
If(Project_Cost_Month = 'Apr',4,
If(Project_Cost_Month = 'May',5,
If(Project_Cost_Month = 'Jun',6,
If(Project_Cost_Month = 'Jul',7,
If(Project_Cost_Month = 'Aug',8,
If(Project_Cost_Month = 'Sep',9,
If(Project_Cost_Month = 'Oct',10,
If(Project_Cost_Month = 'Nov',11,
If(Project_Cost_Month = 'Dec',12,)))))))))))) as Num_Month_Project_Cost,
Date(MonthStart(Project_Cost_Snapshot_Date), 'MMMM-YYYY') as MonthYear,
Date(YearStart(Project_Cost_Snapshot_Date), 'YYYY') as YearMonth,
If(Cost_Code = '3', 'Mechanical Engineering',
If(Cost_Code = '4', 'Controls Engineering', 'Total')) as Engineering_Area
Resident Project_Cost
Order By Project_Cost_Snapshot_Date;

[bhaveshp90]

@vishsaggi I cannot able to see total in the Area. I cannot able to attach qvd here 

[sunny_talwar · Accepted solution]

Maybe create a link table like this

LinkTable:
LOAD DISTINCT Engineering_Area,
	 Engineering_Area as New_Engineering_Area
Resident Project_Cost2
Where Len(Trim(Engineering_Area)) > 0;

Concatenate (LinkTable)
LOAD DISTINCT Engineering_Area,
	 'Total' as New_Engineering_Area
Resident Project_Cost2
Where Len(Trim(Engineering_Area)) > 0;

and now the New_Engineering_Area will have a new value called 'Total'

[bhaveshp90]

thank you @sunny_talwar