4 Replies Latest reply: May 22, 2012 10:38 PM by Bill Ringer Salalima RSS

tag as others

Bill Ringer Salalima

Hi, I have big problem...

 

I have these datasource...

 

ITEMS

     ITEM CODE     CLASS

     LAP001            LAPTOP

     LAP002            LAPTOP

 

DAILY SALES

    ITEM CODE     QTY

     LAP002            2

     LAP001            8

     LAP999            5

     MON888          1

 

So here's my problem, I should have report like this

 

CLASSQTY
LAPTOP10
LAPTOP-OTHERS

5

MONITOR-OTHERS1

 

So, my question is, how can I tagged that ITEM CODE that is not exist in ITEM MAINTENANCE will be tagged as OTHERS... and tagging OTHERS depends on the first 3 characters... so i have another file below for the CLASS CRITERIA

 

CLASS CRITERIA

     CLASS     PREFIXES

     LAPTOP     LAP

     MON          MONITOR

 

Please help. Thanks.

  • tag as others
    Phil Bishop

    Try this:
    Load the ITEMS table and add a second field for [ITEM CODE]
    [ITEM CODE] as [ITEM CODE_ITEM]
    Load the DAILY SALES as you are
    Then CONCATENATE additional rows on the ITEMS table from the DAILY SALES table based on whether ITEM CODE already exists in [ITEM CODE_ITEM]. You can use the CLASS CRITERIA table as a mapping table to set the correct value for CLASS.


    CONCATENATE (ITEMS)
    LOAD DISTINCT
    [ITEM CODE],
    applymap('Class_Map',left([ITEM CODE],3) & '-OTHERS' as CLASS
    Resident
    DAILY_SALES
    Where
    not exists([ITEM CODE_ITEM],[ITEM CODE]);

     

    -Phil

    • tag as others
      Bill Ringer Salalima

      How about other items prefixes that not exist in CLASS CRITERIA should tag as OTHERS... How to do this?

      • tag as others
        Phil Bishop

        The simplest way to accomplish this would be to just use the third parameter of the applymap().
        Something like this (Note that I accidentally left out a right parenthesis from my applymap() code above):
        trim(applymap('Class_Map',left([ITEM CODE],3),null()) & ' OTHERS') as CLASS


        I added the trim() to remove the first space in case NULL is returned for the applymap().
        -Phil

  • tag as others
    Bill Ringer Salalima

    Hi guys,

     

    Thanks for the answers. I already did it, I have found out the wrong position of the mapping load, i put it after the applymap script instead of putting it before the applymap script. That's why it never works... Thanks.