5 Replies Latest reply: May 15, 2014 9:56 AM by Lynette _ RSS

    Missing column label - Summaries

    Lynette _

      Hi,

       

      I have been using the following formula to identify the existance of certain words in a description.

       

      In column "Commodity"

      if(Instr("steel",desc)>1,"Steel",if(Instr("teak",desc)>1,"Teak",.....

       

      The column however never returns the output reqd. The summary is stranger still.. I have setup "Commodity" to display details accross.  Some fields requested are correctly extracted however commodities "steel" and "teak" do not appear as column titles.. Infact the column "commodity" is reflected without a title.

       

      Am i doing an anything incorrectly??

        • Missing column label - Summaries
          Grant Perkins

          Lynette,

           

          Just in case the string you are trying to identify is at the beginning of the field it might be better to use >0 .

           

          if(Instr("steel",desc)>0,"Steel",if(Instr("teak",desc)>0,"Teak",.....

           

          What is your final IF value for when none of the listed values are found? ANd are there less than 336 'nests' in the IF statement? I assume there are as I would expect you to get an error if youy tried to enter more then 36.

           

          If the table is not populated I cannot imagine the summary would be either. Your description suggests that all the commodities are rsulting in a blank field - hence the blank description.

           

          That would happen if the string "Steel" or "Teak" started at the first character. (The INSTR function would return a value "1" but you are looking for greater than 1.)

           

          It could also happen if your final IF function parameter was to leave the field blank if none of the strings were found and if it inadvertently always applied. If your formula does leave it blank you could try entering some other value in order to check that the column does get a heading related to its value.

           

          In the summary - do you get the correct count values for the column?

           

          Of course this may be something else wrong, but these points are worth eliminating first.

           

          Grant

           

          Originally posted by Lynette:

          Hi,

           

          I have been using the following formula to identify the existance of certain words in a description.

           

          In column "Commodity"

          if(Instr("steel",desc)>1,"Steel",if(Instr("teak",desc)>1,"Teak",.....

           

          The column however never returns the output reqd. The summary is stranger still.. I have setup "Commodity" to display details accross.  Some fields requested are correctly extracted however commodities "steel" and "teak" do not appear as column titles.. Infact the column "commodity" is reflected without a title.

           

          Am i doing an anything incorrectly?? /b[/quote]

          • Missing column label - Summaries
            Lynette _

            Grant,

             

            I have rectified the calculated field to reflect > 0.

             

            There are 20 nested IFs. If none of the values are found it should reflect a blank "".

             

            Though the column in the table, where this calculated value is defined, appears blank. The summary  relects a count of values identified. Only challenge is that the the commodity and count do not appear as a crosstab.. All output is collated in a single column..without a heading.

             

            I have used this formula before and it worked just fine.. Not sure what I am doing incorrectly here.

            • Missing column label - Summaries
              Grant Perkins

              Lynette,

               

              Do I understand correctly that you are saying your TABLE column for the Commodity field is always blank? (Your early posts seemed to suggest that most of the records were OK and that only STEEL and TEAK were, for some reason, causing problems.)

               

              If that is the case then the summary will never work as you want it to.

               

              I think I am getting to the point where I would feel much more comfortable if I could see the problem for myself.

               

              Is there a posibility to post to the forum or send the report and your model to me via email?

               

               

              Grant

               

              Originally posted by Lynette:

              Grant,

               

              I have rectified the calculated field to reflect > 0.

               

              There are 20 nested IFs. If none of the values are found it should reflect a blank "".

               

              Though the column in the table, where this calculated value is defined, appears blank. The summary  relects a count of values identified. Only challenge is that the the commodity and count do not appear as a crosstab.. All output is collated in a single column..without a heading.

               

              I have used this formula before and it worked just fine.. Not sure what I am doing incorrectly here. /b[/quote]

              • Missing column label - Summaries
                Nick Osdale-Popa

                Not sure if you've resolved your problem, my suggestion is that 20 nested if statements can get unwieldly. If only 1 of your terms can appear in the field, you can try this type of IF() formula:

                [font="courier"]if(Instr("steel",desc)>0,"Steel","")if(Instr("Teak",desc)>0,"Teak","")...[/font][/quote]If desc had BOTH Teak and Steel, then this type of IF() function would return:

                SteelTeak.

                 

                It's a little easier to maintain and added searches are simple additional +IF(Instr(<search>,<target>)>0,<return>,"") functions

                 

                 

                Just my $.02 worth!

                • Missing column label - Summaries
                  Lynette _

                  Thanks Nick, your 2 cents worth, as you claimed, was a real good measure. Did apply in another context and it made analysis more accurate.

                  Thanks Grant for you support via email. The suggestions made by you help me complete the model... to perfection (i hope).