change field data on load

[spikenaylor1]

My script is as shown below

LOAD [REC ID],

     Project,

     [Date Created],

     [Date Due],

     [Date Closed],

     Month([Date Closed]) as MonthClosed,

     YearName([Date Closed]) as YearClosed,

     If([Date Closed]> [Date Due],2,1) as Overdue

FROM

(ooxml, embedded labels, table is main);

LOAD [PR ID] as [REC ID],

     [Grid ID],

     [Root Cause Tier 1],

     [Root Cause Tier 2],

     [Root Cause Tier 3]

FROM

(ooxml, embedded labels, table is [Root Cause]);

The data in [Root Cause Tier 1] contains a mixture of values

I would like to change all the instances in [Root cause Tier 1] from material to materials

tried a few options but unsuccessful at the moment.

Could anyone help out?

many thanks for looking.

[sunny_talwar]

Michael Taylor wrote:

The data in [Root Cause Tier 1] contains a mixture of values

Not sure I understand what you mean by this? Can you elaborate may be?

[trdandamudi · Accepted solution]

Try below script:

LOAD [REC ID],

     Project,

     [Date Created],

     [Date Due],

     [Date Closed],

     Month([Date Closed]) as MonthClosed,

     YearName([Date Closed]) as YearClosed,

     If([Date Closed]> [Date Due],2,1) as Overdue

FROM

(ooxml, embedded labels, table is main);

LOAD [PR ID] as [REC ID],

     [Grid ID],

     If([Root Cause Tier 1]='material','materials',[Root Cause Tier 1]) as [Root Cause Tier 1]

     [Root Cause Tier 2],

     [Root Cause Tier 3]

FROM

(ooxml, embedded labels, table is [Root Cause]);

Edit: Added missing bracket as mentioned by Sunny.

[sunny_talwar]

You missed a parenthesis to close the if statement

[spikenaylor1]

oh those simple solutions when I spend too much time thinking about things.

Many thanks

[trdandamudi]

That always happen... Happy Qliking....

[spikenaylor1]

going on from this, how to do a couple of changes, ie change material to materials and also method to methods

I have tried to get a or statement in and  multiple if stement if, tried a concentate load but all to no avail.

Looked for this type of issue, but can't find anything suitable for my issues.

any help much appreciated.

[sunny_talwar]

Can you elaborate and may be share some sample data to explain what you are looking to get?

[trdandamudi]

May be as below:

LOAD [REC ID],

     Project,

     [Date Created],

     [Date Due],

     [Date Closed],

     Month([Date Closed]) as MonthClosed,

     YearName([Date Closed]) as YearClosed,

     If([Date Closed]> [Date Due],2,1) as Overdue

FROM

(ooxml, embedded labels, table is main);

LOAD [PR ID] as [REC ID],

     [Grid ID],

     If([Root Cause Tier 1]='material','materials',If([Root Cause Tier 1]='method','methods',[Root Cause Tier 1])) as [Root Cause Tier 1]

     [Root Cause Tier 2],

     [Root Cause Tier 3]

FROM

(ooxml, embedded labels, table is [Root Cause]);

Hope this helps...

[spikenaylor1]

Thanks very much

I now feel stupid as I clearly didn't understand the formula and I have written formula like that in excel

I need to read and follow the formula help much more and stop overthinking things

Thanks again until the next time

Spikenaylor