14 Replies Latest reply: Mar 7, 2017 10:30 PM by Fabrizio Giorgio RSS

    Display 0/1 fields as text in a Table Box

    Fabrizio Giorgio

      Hi there,

       

      I believe this is a simple question as I am new to report writing in Qlikview

       

      I am connecting into a database via ODBC.

       

      I need to display a field in a Table Box with 0 and 1 values.

      The field name from the loaded table is “Active”

       

      1 should display “Active” and 0 should display “Inactive”…

       

      How do I go about displaying Active / Inactive in my Table box instead of 0/1?

       

      I believe what I need to do is load the required field (Active) as another field in the script (say for example: Active_Logic) that translates 1 into Active and 0 into Inactive, then display the values from Active _Logic in my Table Box.

       

      If the above is correct, can anyone help with the syntax for this?

        • Re: Display 0/1 fields as text in a Table Box
          Peter Cammaert

          Do you really need to use a Table Box? Then yes, add a translation of your Active field values to the table that contains the field; You don't need to create a new table per se.

           

          OTOH Table Boxes are not your typical QlikView object with an endless series of configuration options. They are very limited in what they can do and most of the time they simply serve as debugging tools.

          For example, a simple list box can display an expression instead of a field value and as such do the translation on-the-fly without the need to change your data model.

           

          For example, create a list box, select <expression> from the list of available fields and enter something like this in the Expression Editor:

           

          =IF (Active=1, 'Active', 'Inactive')

            • Re: Display 0/1 fields as text in a Table Box
              Fabrizio Giorgio

              Hi Peter,

               

              Thanks for your feedback, I need all the advice I can get as I am only new to this.

               

              What I am doing is setting up very simple reports that can be run and filtered by the user.

               

              Our users are used to table reports (not pie charts etc...)

              My basic report structure consist of a series of list boxes that act as filters, and a Table Box that displays the results of the filters. The results from a table box can then be displayed / printed / exported to excel which is exactly what we need at this point in time. 

               

              In this instance I'm setting up a report to list company suppliers including all their details if they are active or inactive.


              I have used the expression  =IF(Active = 0, 'No','Yes') in the List box to display Yes / No in the "Active" Filter... I now need to dispay the results in the table box as Active / Inactive.... (or yes/no) hence my question...


              In your opinion, is there anything better I can use to display results in a table format?


              Thanks again for your input...

              • Re: Display 0/1 fields as text in a Table Box
                Fabrizio Giorgio

                Hi Peter,

                 

                when you say:

                 

                "add a translation of your Active field values to the table that contains the field; You don't need to create a new table per se."

                 

                how do I do this to translate 1=Active and 0=Inactive in the Table Box?

                 

                If you could let me know that would be appreciated...

                 

                Thanks.

                 

                F.Giorgio

                • Re: Display 0/1 fields as text in a Table Box
                  Fabrizio Giorgio

                  Hi Peter,

                   

                  I came back to this with fresh eyes and had another look at some demo reports and found that they were using (straight table) charts instead of table boxes.

                   

                  Took only a couple of minutes to figure out how to insert a dimension using an expression...

                   

                  thanks to all for your contributions, but also thanks Peter for questioning my need to use a table box...

                • Re: Display 0/1 fields as text in a Table Box
                  mayilvahanan ramasamy

                  Hi

                   

                  Try like this

                   

                  Load *, ifActive = 1, 'Active', 'Inactive') as Active_Logic from Souretablename;

                   

                  And use Active_Logic field in table box and wherever its required.

                    • Re: Display 0/1 fields as text in a Table Box
                      Fabrizio Giorgio

                      Hi Mayil,

                       

                      thanks for your help...

                       

                      I think we are on the right track but not there yet.

                       

                      Here is my script:

                       

                      ODBC CONNECT32 TO meillive (XUserId is XXXXXXXXXXXXX, XPassword is XXXXXXXXX);

                      SQL SELECT *

                      From PUB.creditor;

                      Load *, If(Active = 1, 'Active', 'Inactive') as Active_Logic;

                       

                      This script runs without any issues but I cannot find the field Active_Logic to map into my Table Box.

                       

                      To be clear, the values 0 / 1 are in the field  pub.creditor.active

                       

                      I have also tried:

                      Load *, If(Active = 1, 'Active', 'Inactive') as [Active_Logic];

                      Load Active, If(Active = 1, 'Active', 'Inactive') as [Active_Logic];

                      Load Active, If(Active = 1, 'Active', 'Inactive') as 'Active_Logic';


                      No errors, but cannot find Active_Logic in the list of fields...


                      am I missing something?


                      Your help is greatly appreciated...


                      Thanks.


                      F.Giorgio


                    • Re: Display 0/1 fields as text in a Table Box
                      Avinash R

                      Try like this

                      Load *, If(pub.creditor.active=1, 'Active', 'Inactive') as [Active_Logic];



                      • Re: Display 0/1 fields as text in a Table Box
                        Marco Wedel

                        maybe you could generate dual values using the dual() function to be able to use this field as boolean value.

                          • Re: Display 0/1 fields as text in a Table Box
                            Peter Cammaert

                            Wel Marco, you did it ! Congratulations, a round number, legend status and all well-deserved.

                            • Re: Display 0/1 fields as text in a Table Box
                              Fabrizio Giorgio

                              Hi Marco,

                               

                              Thanks for your input.

                               

                              Are you able to guide me further on how to do this?

                               

                              So far, I have tried changing my script as per the suggestions in this thread and still cannot see the new table (field) Active_Logic in the list of available fields. the script runs in many ways without giving me any errors, I just can't see the new table that's all. and I know that in my source table (active field) the values are 1/0 because I can see them in my Table box.

                               

                              For all it's worth, if it makes any difference, the database i'm connecting to is a Progress database.

                               

                              I know i can create new tables as I have some demo reports and there is the "as" command in the script here and there... however not sure why i cannot see the new table we are creating.

                               

                              Could it be that the table has been created but I cannot see it in the available fields? I have toggled "Show fields from table" to all the options (All tables / All Tables qualified / creditor) but still cannot see Active _Logic in the Available fields..

                               

                              Should Active_Logic display in the list of available fields or is there another command I need to put into the script to display this...

                               

                              As I said at the beginning of the thread, i'm very new to this so I'm still trying to figure these things out...

                               

                              Any help would be appreciated.

                               

                              Thanks...

                               

                              F.Giorgio.