When value is 259 search in excel file

[Simon4]

Hi all,

I am working with a live database, in this database there is information about items and in which itemgroup they are. Old/unused  items are grouped in a group called "yet to be determend". Unfortunatly because of reasons I will not get into these items will never be categorized in the database.

To make a proper analysis I still categorized them in an external Excel file. 

Now I need the following solution:

When Itemgroupid=259 search In excel file for Itemcode and lookup Itemgroup. 

I made a simple script with the data I am using. 

How do I approach this problem?

Thank you kindly in advance,

Simon

[rubenmarin1 · Accepted solution]

Hi, you can load the excel a mapping table, and while loading items use an applymap:

If(FK_ITEMGROUP=259,Applymap('maptable',CODE),"FK_ITEMGROUP") as Itemgroupid

[Simon4]

Thankyou for your repply.

I am not sure if I understand correctly. This should be it right? Why am I getting the red text?

[rubenmarin1]

That line should go in the Item table, instead of loading Itemgroupid as it's loading now use that line.

The mapping table should be created before the item table so it's available for applymap.

And the applymap should search for CODE, as the field Itemcode is not yet created when loading the item table.

[Simon4]

Hi,

I am still having trouble getting it running. What do I have to change?

To clarify, I changed the Excel table.

Thank you for taking your time to respond! You are helping me a lot, know it is appreciated.

Simon

[rubenmarin1]

Hi, the ITEMGROUP table needs the 'as' to rename the fields.

If doesn't works it will be easier for me if you add additional info like the error you get or post the results you have and the ones you expect.

[Simon4]

It doesn't work.

When I load this code:

I get this error message:

I'll try to explain what I expect a little bit better than I did before:

When Itemgroupid=259 I need Qlik to look at the excel file what the actual itemgroupid is and overwrite the itemgroupid from table Item (so the 259). This way it can lookup the Itemgroup in table "Itemgroup" and make the data complete. 

Maybe it is a possibility to join the excel file with the table "Item" and overwrite the itemgroupid? I looked in to this but could not find a solution.

[rubenmarin1]

That's because you are using Itemgroup, paste the line as i posted in the first answer :

If(FK_ITEMGROUP=259,Applymap('maptable',CODE),"FK_ITEMGROUP") as Itemgroupid

Only Change maptable to the mapping table name:

If(FK_ITEMGROUP=259,Applymap('Categorized',CODE),"FK_ITEMGROUP") as Itemgroupid

also i said that...That line should go in the Item table, instead of loading Itemgroupid as it's loading now use that line.

... the exel hast the group name or the id? If it has the name you'll need some extra treatment.

1. Load Item as it was initially and do a left join of the ITemgroup table to have itemgroup in the same table:

2. do a load of this table loading Itemid, itemcode and itemgroup with the check and applymap:

tmpItem:
// Load as Item was created initally
;
Left Join (tmpItem)
LOAD // Itemgroup table as it was initially loaded
;
Item:
NoConcatenate
LOAD
  Itemid,
  Itemcode
  If(Itemgroupid=259,Applymap('maptable',itemcode),Itemgroup) as Itemgroup
Resident tmpItem;
DROP Table tmpItem

[Simon4]

No errors in this code!

But it is still not quite right. 

this is the result whitout 

Item:
NoConcatenate
LOAD
  Itemid,
  Itemcode
  If(Itemgroupid=259,Applymap('maptable',itemcode),Itemgroup) as Itemgroup
Resident tmpItem;
DROP Table tmpItem

The blue category is the itemgroupid "259"

And this is the full code.

It looks to me that everything in the category 259 just switched to "Other".

[Simon4]

I checked the insights.

It now loads every Itemcode with Itemgroupcode "259" as a category. Every item has become its own category.