Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor III

How to combine three fields values into one field ?

Hello, 

I've 3 fields for managers as shown below; 

1.PNGHow 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

Labels (2)
5 Replies
Highlighted
Honored Contributor II

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

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.

Highlighted
Contributor III

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

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
Highlighted
Contributor II

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

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!!!

Highlighted
Honored Contributor II

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

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..

Highlighted
Valued Contributor

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

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