topic Re: Display 0/1 fields as text in a Table Box in QlikView

Display 0/1 fields as text in a Table Box

Anonymous — Mon, 20 Feb 2017 06:09:37 GMT

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 — Mon, 20 Feb 2017 08:45:06 GMT

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

MayilVahanan — Mon, 20 Feb 2017 08:55:57 GMT

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

Anonymous — Mon, 20 Feb 2017 22:45:18 GMT

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

Anonymous — Mon, 20 Feb 2017 23:35:31 GMT

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

avinashelite — Tue, 21 Feb 2017 05:39:36 GMT

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

sdmech81 — Tue, 21 Feb 2017 05:57:46 GMT

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

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

MarcoWedel — Tue, 21 Feb 2017 17:36:28 GMT

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 — Tue, 21 Feb 2017 18:23:29 GMT

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

MarcoWedel — Tue, 21 Feb 2017 18:33:55 GMT

thank you, Peter.

It was worth it.

Marco

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

Anonymous — Tue, 21 Feb 2017 22:21:25 GMT

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.

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

Anonymous — Tue, 21 Feb 2017 22:24:24 GMT

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

Anonymous — Tue, 21 Feb 2017 22:25:47 GMT

Thanks Avinash,

Did this but still cannot see Active_Logic in the list of available fields...

any other ideas on how to achieve this or why i might not be seeing the new table in the list of available fields?

Thanks,

F.Giorgio

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

Anonymous — Tue, 21 Feb 2017 22:27:16 GMT

Hi Sachin,

tried this, but stll cannot see Active_Logic in my list of available fields..

Any further ideas?

Thanks..

F Giorgio

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

Anonymous — Wed, 08 Mar 2017 03:30:54 GMT

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