topic How to combine three fields values into one field ? in QlikView

How to combine three fields values into one field ?

bhaveshp90 — Sat, 16 Nov 2024 03:56:07 GMT

Hello,

I've 3 fields for managers as shown below;

How can I combine Controls_Manager, Software_Manager, Mechanical_Manager into one new field in the load script?

Cost_temp:
LOAD 
[Project Number]&'-'&Region AS Link_Key,
[Project Number], 
Region, 
[Cost Area],
Var,
Quoted					
FROM [C:\Bhavesh\Qvd Files\VIEW_WSR_SAP_ORA_WBS_COST.qvd](qvd);

right join (Cost_temp)
LOAD 
PROJECT_NUMBER&'-'&REGION AS Link_Key,
D_PROJECT_KEY, 														
PROJECT_NUMBER, 												
MECHANICAL_MANAGER,															
SOFTWARE_MANAGER, 														
CONTROLS_MANAGER,														
REGION 																			
FROM [C:\Bhavesh\Qvd Files\DIM_PROJECT.qvd] (qvd);

Cost:
NoConcatenate
Load Distinct
*,
Var/Quoted as [Budget flag]
Resident Cost_temp;
Drop table Cost_temp;
Exit Script;

Attached is the file for reference

thanks

Bhavesh

Re: How to combine three fields values into one field ?

trdandamudi — Fri, 05 Apr 2019 13:04:47 GMT

Is this what you are looking for:

write the below code in your script window:

Controls_Manager &'|' &Software_Manager, &'|' &Mechanical_Manager as New_Field

Hope this helps.

Re: How to combine three fields values into one field ?

bhaveshp90 — Fri, 05 Apr 2019 13:09:34 GMT

Hi, I'm looking to combine all the values into one field, I don't want to concatenate them. Like as shown below

Manager

Tautges Steve

Alfredson Tom

Gill Andrew

UNKNOWN

Jones Michael

Tautges Steven

N/A

Re: How to combine three fields values into one field ?

Marcos_rv — Fri, 05 Apr 2019 13:18:31 GMT

Can you give us a clearer example of what each qvd contains and the result you hope to achieve? It would be easier to help you. regards!!!

Re: How to combine three fields values into one field ?

trdandamudi — Fri, 05 Apr 2019 13:26:44 GMT

Hope I am understanding this in the correct way. You want a manager field which is the combination of three manager fields. I think you want this as a separate table. Here is a sample script:

Data:

Load * Inline [
MECHANICAL_MANAGER,SOFTWARE_MANAGER,CONTROLS_MANAGER,Amount
John,Smith,Kevin,1000
];

Managers:
Load distinct
MECHANICAL_MANAGER as Manager
Resident Data;
Concatenate
LOAD distinct
SOFTWARE_MANAGER as Manager
Resident Data;
Concatenate
LOAD distinct
CONTROLS_MANAGER as Manager
Resident Data;

Now your Manger field will have all the mangers. Hope this helps..

Re: How to combine three fields values into one field ?

johnca — Fri, 05 Apr 2019 14:57:05 GMT

Perhaps like this? But, one question...does it need to be joined to the table Cost (by Project key or Id or other)? Just add that field to each of the load statements below;

Combined:
NoConcatenate
Load distinct
CONTROLS_MANAGER as New_Manager
Resident Cost
where not isnull(CONTROLS_MANAGER);

Concatenate(Combined)
Load distinct
MECHANICAL_MANAGER as New_Manager
Resident Cost
where not exists(New_Manager,MECHANICAL_MANAGER)
and not isnull(MECHANICAL_MANAGER);

Concatenate(Combined)
Load
SOFTWARE_MANAGER as New_Manager
Resident Cost
where not exists(New_Manager,SOFTWARE_MANAGER)
and not isnull(SOFTWARE_MANAGER);

Result:

_N/A_ 1
Alfredson Tom 1
Gill Andrew 1
Jones Michael 1
Tautges Steve 1
Tautges Steven 1
UNKNOWN 1

V/r,

John