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.