Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

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

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.

1 Solution

Accepted Solutions
jerem1234
Valued Contributor II

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

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!

View solution in original post

12 Replies
jerem1234
Valued Contributor II

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

Something like:

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

Hope this helps!

Not applicable

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

Jeremiah,

Where am I supposed to put this code?

After LOAD statement?

Sorry, I am new to Qlikview.

Thx.

jerem1234
Valued Contributor II

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

Something like:

Table1:

Load

Item,

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

Field1,

Field2,

...

FROM ...;


Not applicable

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

Let me try it.

Thanks for the response.

Not applicable

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

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
Valued Contributor II

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

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!

View solution in original post

Not applicable

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

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

Not applicable

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

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
Valued Contributor II

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

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!