Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bhaveshp90
Creator III
Creator 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
trdandamudi
Master II
Master II

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.

bhaveshp90
Creator III
Creator III
Author

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
Marcos_rv
Creator II
Creator II

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

trdandamudi
Master II
Master II

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

johncaqc
Specialist
Specialist

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