Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
spikenaylor1
Creator
Creator

change field data on load

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.

1 Solution

Accepted Solutions
trdandamudi
Master II
Master II

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.

View solution in original post

10 Replies
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
Master II
Master II

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
Creator
Creator
Author

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

Many thanks

trdandamudi
Master II
Master II

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

spikenaylor1
Creator
Creator
Author

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
Master II
Master II

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
Creator
Creator
Author

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