topic Category Problem in QlikView

Category Problem

andrea90casa — Tue, 12 Sep 2017 09:09:12 GMT

Hi at all,

I have a table with some articles and another table with categories.

I have created a left join formula in order to take all the value in the first table and take the value of category for each articles that had a specific code.

I saw that when i show the amount of sales by category there is a field with empty simbol '-' . So that simbol is the total amount of sales for the articles that don't have a category code.

So i would like to add a formula in the script that assign at each article that doesn't have the category code a generic name of category (for example 'Unknown'). I tried to use an if formula in the second table (table with category name for each code) but it is not works because in that table any code has a category name. So i have to add that formula in the previous script but how can i do?

//-----------------------------LOAD FIRST TABLE DATA BY QVD FILE------------------------------------------//

Articles:

LOAD

FROM C:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/Articles.qvd(qvd);

//-----------------------------LOAD AND JOIN SECOND TABLE------------------------------------------//

Left Join

LOAD

Category_Code

Category Name as CATEGORIA

FROM

(ooxml, embedded labels, table is Category);

Re: Category Problem

karthikoffi27se — Tue, 12 Sep 2017 09:13:40 GMT

Hi Andrea,

Please use the below formula

NULLASVALUE * ;

SET vNullValue = 'Unknown';

This will create a Unknown value for all the null value fields.

Many Thanks

Karthik

Re: Category Problem

andrea90casa — Tue, 12 Sep 2017 09:20:12 GMT

Thank you so much Karthik

Hi tried with this, when I was waiting someone help me, and it should be works:

Articles:

LOAD

IF(IsNull(Category),'Unknown',Category) as Category_00

FROM C:\xxxxxxxxxxxxxxxxxxxxxxxxxxxxArticles.qvd(qvd);

DROP Field Category FROM Articles;

RENAME Field Category_00 TO Category;

Re: Category Problem

mostwanted123 — Tue, 12 Sep 2017 09:22:34 GMT

Hi Andrea,

Try this,

Use ApplyMap() function.

ABC:

Mapping Load

Category_Code

Category Name as CATEGORIA

FROM

(ooxml, embedded labels, table is Category);

Now, in the Articles table, write

ApplyMap('ABC',Category_Code,'Unknown') as CATEGORIA

This will assign "Unknown" that doesn't have any Category Code.

Also, make sure you load the excel sheet before the Articles table.