topic Re: change field data on load in QlikView

change field data on load

spikenaylor1 — Mon, 01 Aug 2016 14:40:33 GMT

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.

Re: change field data on load

sunny_talwar — Mon, 01 Aug 2016 15:06:10 GMT

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?

Re: change field data on load

trdandamudi — Mon, 01 Aug 2016 15:08:11 GMT

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.

Re: change field data on load

sunny_talwar — Mon, 01 Aug 2016 15:11:09 GMT

You missed a parenthesis to close the if statement

Re: change field data on load

spikenaylor1 — Mon, 01 Aug 2016 16:48:05 GMT

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

Many thanks

Re: change field data on load

trdandamudi — Mon, 01 Aug 2016 17:35:39 GMT

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

Re: change field data on load

spikenaylor1 — Thu, 04 Aug 2016 13:55:58 GMT

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.

Re: change field data on load

sunny_talwar — Thu, 04 Aug 2016 14:25:11 GMT

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

Re: change field data on load

trdandamudi — Thu, 04 Aug 2016 14:41:50 GMT

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...

Re: change field data on load

spikenaylor1 — Thu, 04 Aug 2016 16:31:43 GMT

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

Re: change field data on load

trdandamudi — Thu, 04 Aug 2016 17:45:06 GMT

You are welcome. Sometimes it happens, for simple things we think for hours but I am glad that I am able to help you. Have a nice day...