topic Re: When value is 259 search in excel file in App Development

When value is 259 search in excel file

Simon4 — Fri, 07 Oct 2022 09:46:37 GMT

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

Re: When value is 259 search in excel file

rubenmarin — Fri, 07 Oct 2022 10:21:27 GMT

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

Re: When value is 259 search in excel file

Simon4 — Fri, 07 Oct 2022 10:58:31 GMT

Thankyou for your repply.

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

Re: When value is 259 search in excel file

rubenmarin — Fri, 07 Oct 2022 11:01:46 GMT

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.

Re: When value is 259 search in excel file

Simon4 — Fri, 07 Oct 2022 12:14:37 GMT

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

Re: When value is 259 search in excel file

rubenmarin — Fri, 07 Oct 2022 12:26:47 GMT

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.

Re: When value is 259 search in excel file

Simon4 — Fri, 07 Oct 2022 12:44:05 GMT

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.

Re: When value is 259 search in excel file

rubenmarin — Fri, 07 Oct 2022 13:00:21 GMT

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

Re: When value is 259 search in excel file

Simon4 — Fri, 07 Oct 2022 13:43:33 GMT

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

Re: When value is 259 search in excel file

Simon4 — Fri, 07 Oct 2022 13:55:23 GMT

I checked the insights.

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

Re: When value is 259 search in excel file

rubenmarin — Tue, 11 Oct 2022 06:23:40 GMT

That 'other' it's a configuration in the pie chart, you can disable to show that.

To check data it will be better to do in a table chart.

Re: When value is 259 search in excel file

rubenmarin — Tue, 11 Oct 2022 06:26:35 GMT

That would happen if the values of the Applymap() are not found in the mapping table, check wich values the excel have and in Appplyap use the field that matches those values from the database.

Re: When value is 259 search in excel file

Simon4 — Tue, 11 Oct 2022 06:27:05 GMT

Hi @rubenmarin ,

Thank you for all you time and patience.

After a lot of trying I have made it work. Could not have done it whitout your help.

Thanks lots,