5 Replies Latest reply: Jul 19, 2012 4:16 PM by mayilvahanan ramasamy RSS

    List Box Options

      COnsider I have below table in which City and State field is marked as blank.

       

      NameCityStatePhone
      Rober DeNiroHollywoodCA
      Will SmithNew YorkNY
      John DoeXXX-YYZ-ZZZZ
      Jane DoeZZZ-XXY-YYYY

       

      Now if I created list box with state names, it will just show me CA and NY values. Is there any way to show in list box theer are some blank values in this field?

        • Re: List Box Options
          mayilvahanan ramasamy

          Hi,

           

               Try like this,

           

               Load *,if(Len(Trim(State)) >0, State,'N/A') as State from tablename;

           

               (or)

           

                Load *,if(Len(Trim(State)) >0, State,Null()) as State from tableName;

           

          Hope it helps

          • Re: List Box Options
            victor montero

            Hi AndrePeter,

            Create a new field within the load

             

             

             

            If(State=null(), ’Blank’ , State) as  State_Global

             

            or

             

            If(State='' , ’Blank’ , State) as  State_Global

            • Re: List Box Options
              Nagaian Krishnamoorthy

              In your load script, before loading the data, add the following statement:

               

              NullAsValue City, State;

               

              Then create listboxes for the fields City and State. You will see a blank line in the listbox.

               

              Hope this helps.

              • Re: List Box Options

                Thanks for all the responses. It was helpful. But there is small isssue now. Sorry i should have made my issue very clear. I am creating this table using join of two tables.

                 

                TableA

                [

                Name,

                City,

                Phone

                ]

                 

                TableB

                [

                City,

                State

                ]

                 

                Then on dashboard I show view of combined table shown in my proginal post. I tried below three options-

                A) I created user defined variable on dashboard State_Val=(if len(State) = 0, 'Blank',State)

                    And tried to use this variable in Listbox, this didn't work.

                B) Later I tried to use left join between Table A and Table B, here i tried to add extra vaiable "(if len(State) = 0, 'Blank',State) As State_Val). And later I tried to use this variable in listbox, it didn't work.

                C) Later I tried to use left join between Table A and Table B, here i tried to add extra vaiable "(if len(State) = null() 'Blank',State) As State_Val). And later I tried to use this variable in listbox, it didn't work either.

                  • Re: List Box Options
                    mayilvahanan ramasamy

                    HI,

                     

                         After join both table like this,

                     

                        

                    TableA

                    Load

                    Name,

                    City,

                    Phone

                    from TableA;

                     

                    Temp;

                    join(TableA)

                    Load

                    City,

                    State

                    from tableB;

                     

                    Load * , if(len(trim(State)) > 0, State,'Blank') as State resident TableA;

                     

                    Drop table Temp;

                     

                    Then use , State field in listbox