Display 0/1 fields as text in a Table Box

[Anonymous]

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?

[Peter_Cammaert · Accepted solution]

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')

[MayilVahanan]

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.

[Anonymous]

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

[Anonymous]

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

[avinashelite]

Try like this

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

[sdmech81]

May be this:

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

Load *,

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

SQL SELECT *,

From PUB.creditor;

In first * specify fileds

[MarcoWedel]

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

[Peter_Cammaert]

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

[MarcoWedel]

thank you, Peter.

It was worth it.

Marco