Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I am stuck here and need help.
I am loading data from Excel and once loaded I need to change some cells of a table depending on a condition.
The sample data is as follows:
In the Load script there is no issue loading both columns, Business Unit and Business Segment.
However, I need to reassign all the Business Segment cells containing the word "Project" to the Sales Projects Business Unit. This is the desired result:
As you may notice, at Load time there are Business Segment cells that belong to Sales Projects, that must remain like that.
I tried do a Mapping, like this:
Move_to_BU_from_BS:
MAPPING LOAD * INLINE
[
'Business Segment', 'Business Unit'
Project Product, Sales Projects,
Project Parts, Sales Projects,
Project Special, Sales Projects,
Project International, Sales Projects
];
but once loaded, Business Unit cannot be used again in the mapping of Business Segment. I get this error message: Field names must be unique within table.
This is caused because Business Unit has already been loaded and I am attempting to use the name again in the mapping...
Maybe mapping is not the right solution, so I will appreciate any hints and help to find the correct way of doing it.
I am attaching also the Excel file with both CurrentData and DesiredResult tables.
Thank you!
datalakeyu, your hint led me to the solution, thanks so much!
This is what I did:
[CurrentData]:
LOAD
[Business Unit],
if(WildMatch([Business Segment],'Project*'),'Sales Projects',[Business Unit]) as TempBU2,
[Business Segment],
Value
FROM [lib://160822/RelocationData.xlsx]
(ooxml, embedded labels, table is CurrentData);
Then I use only for my charts TempBU2.
Please try the following expression, and you need modify the expression a little
If(WildMatch( [Business Segment] , 'Project*'), 'Sales Projects',
If(Match( [Business Segment], ' '), ' ', ' ')) as new_Business_Segment
Thanks for the soon reply, Haikuo Yu
I understand the wildmatch but where do I assign the Business Unit? I do not want a new field like new_Business_Segment (unless it is a temporary step), because I need to relocate the cells that contain Project* to the Sales Projects Business Unit... I need to generate charts with the "DesiredResult" table and if I generate new_Business_Segment I can't relate the data to the Sales Projects Business Unit.
I've been thinking in two steps... but without solving the issue of using the same field name, I cannot find the solution.
Any other hints?
datalakeyu, your hint led me to the solution, thanks so much!
This is what I did:
[CurrentData]:
LOAD
[Business Unit],
if(WildMatch([Business Segment],'Project*'),'Sales Projects',[Business Unit]) as TempBU2,
[Business Segment],
Value
FROM [lib://160822/RelocationData.xlsx]
(ooxml, embedded labels, table is CurrentData);
Then I use only for my charts TempBU2.
Glad to learn from you. Thanks!