Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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&'-'®ION 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
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.
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 |
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!!!
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..
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