How can I overwrite a category for one of the items?

[#1]

Hello everyone,

I am working on a file with bunch of item names which belongs to different categories. One of the items has to go under discontinued category. How is this possible with Edit Script window? How can i change the category of that one particular item manually.I really don't want to go excel file and change it from there.

Thanks for reading.

[jerem1234]

Something like:

If(Item= 'ABC', 'Discontinued', Category) as Category

Hope this helps!

[#3]

Jeremiah,

Where am I supposed to put this code?

After LOAD statement?

Sorry, I am new to Qlikview.

Thx.

[jerem1234]

Something like:

Table1:

Load

Item,

If(Item= 'ABC', 'Discontinued', Category) as Category,

Field1,

Field2,

...

FROM ...;

[#5]

Let me try it.

Thanks for the response.

[#6]

This is the error I get:

Field names must be unique within table.

LOAD [ITEM NUMBER],

  If([ITEM NUMBER]='ITEM XXX','Discontinued',CATEGORY) as CATEGORY,

  COLOR,

     CATEGORY,

     [2013 Sales],

     [2013 Qty],

     [2013 Ave. P.]

[jerem1234 · Accepted solution]

LOAD [ITEM NUMBER],

  If([ITEM NUMBER]='ITEM XXX','Discontinued',CATEGORY) as CATEGORY,

  COLOR,

     CATEGORY,

     [2013 Sales],

     [2013 Qty],

     [2013 Ave. P.]

Remove the second CATEGORY field:

LOAD [ITEM NUMBER],

  If([ITEM NUMBER]='ITEM XXX','Discontinued',CATEGORY) as CATEGORY,

  COLOR,

     [2013 Sales],

     [2013 Qty],

     [2013 Ave. P.]

Hope this helps!

[#8]

Hi Korhan,

An If statement will give you the answer you are looking for, but if there is the potential for other items to be discontinued (which I think would be likely?), then I would suggest creating a mapping table to do this for you, which is much easier to maintain.

Have a look at this blog post maybe on the subject http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Essentially you want something like the below:

Discontinue_Map:

Mapping Load * inline [

Item, Category

ABC, Discontinued

];

Then when you are loading your category field use the map

Applymap('Discontinue_Map',[Item Field Name Here], [Category Field Name Here]) As Category

Any item (ABC) will have it's category mapped to discontinued, everything else will load their category as standard

If you then have more items, you just add them to your mapping. This would be ideally held externally rather than inline, but you get the idea hopefully

hope that help

Joe

[#9]

Jeremiah,

Thx for the answer.

Where can I find these statements for Edit Script window. I just want to have better understanding.

Can you point me in the right direction?

[jerem1234]

Look in the qlikview help and search for edit script (Click on Help at the top of your dashboard and then New to Qlikview?). There it will talk about the script and then you can access other things related like syntax and various functions you can use.

Also, searching qlikcommunity and/or posting discussions is a great way to learn. There's a lot of content here as well as knowledgeable folks that can answer your questions.

Hope this helps!