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



           ITEM CODE     CLASS

           LAP001            LAPTOP

           LAP002            LAPTOP



          ITEM CODE     QTY

           LAP002            2

           LAP001            8

           LAP999            5

           MON888          1


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






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

          [ITEM CODE],
          applymap('Class_Map',left([ITEM CODE],3) & '-OTHERS' as CLASS
          not exists([ITEM CODE_ITEM],[ITEM CODE]);



            • 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().

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