Category Problem

[andrea90casa]

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);

[karthikoffi27se · Accepted solution]

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

[andrea90casa]

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;

[mostwanted123]

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.