topic Re: How can we create another field combining two fields? in QlikView

How can we create another field combining two fields?

bhaveshp90 — Sat, 16 Nov 2024 04:51:50 GMT

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.

Re: How can we create another field combining two fields?

vishsaggi — Wed, 02 Jan 2019 20:36:27 GMT

Can you let us know your expected output you want to see in your Engineering_Area field? And your qvw file is failing and unable to open it.

Re: How can we create another field combining two fields?

sunny_talwar — Wed, 02 Jan 2019 20:37:55 GMT

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

Can you elaborate on what exactly do you mean by this?

Re: How can we create another field combining two fields?

bhaveshp90 — Wed, 02 Jan 2019 20:44:19 GMT

Hi @sunny_talwar, I am trying to add Total (Cost_Code = '3' and Cost_Code = '4') to Engineering_Area.

So that my Engineering_Area has three fields; Mechanical Engineering, Controls Engineering, and Total.

Re: How can we create another field combining two fields?

bhaveshp90 — Wed, 02 Jan 2019 20:48:03 GMT

@vishsaggi I want to Add Total to my Engineering_Area; where Total = (Cost_Code ='3' + Cost_Code = '4')

I've attached the file again now.

Re: How can we create another field combining two fields?

vishsaggi — Thu, 03 Jan 2019 08:09:37 GMT

Did you replaced the file onto your first thread or did you missed attaching. I do not see the qvw file attached.

Re: How can we create another field combining two fields?

bhaveshp90 — Thu, 03 Jan 2019 13:37:12 GMT

@vishsaggi please find the attachment here, I've attached in my question but I don't know why you are unable to see it.

Re: How can we create another field combining two fields?

vishsaggi — Thu, 03 Jan 2019 13:57:59 GMT

Ok your Cost_Code field has two values 3 and 4 when you say you want total what should happen when you select total. As at a time either you will have costcode 3 or 4 not both right?

Re: How can we create another field combining two fields?

bhaveshp90 — Thu, 03 Jan 2019 14:06:25 GMT

@vishsaggi I am trying to make Area as a filter in my report;

When I select Total it should show both values Sum(Cost_Code = 3 + Cost_Code =4)

My Area should have 3 fields I can select = Cost_Code =3, Cost_Code =4, Sum(Cost_Code = 3 + Cost_Code =4)

Re: How can we create another field combining two fields?

bhaveshp90 — Thu, 03 Jan 2019 14:57:37 GMT

@sunny_talwar Can you please help me with this problem?

Re: How can we create another field combining two fields?

vishsaggi — Thu, 03 Jan 2019 15:18:57 GMT

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;

Re: How can we create another field combining two fields?

bhaveshp90 — Thu, 03 Jan 2019 15:54:38 GMT

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

Re: How can we create another field combining two fields?

sunny_talwar — Fri, 04 Jan 2019 12:14:17 GMT

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'

Re: How can we create another field combining two fields?

bhaveshp90 — Fri, 04 Jan 2019 21:19:03 GMT

thank you @sunny_talwar