Overwriting already loaded data (Qlik Sense)

[demonioazul]

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!

[Anonymous]

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

[demonioazul]

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?

[demonioazul · Accepted solution]

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.

[Anonymous]

Glad to learn from you. Thanks!