Solved: Show DB2 Boolean True/False as Y/N in Qlickview - Qlik Community

Report Inappropriate Content · ‎2013-10-18

Hello,

I have a Boolean field in my DB2 database that is Null, True or False. When selecting in DB2 I can select the True and False values with the next statement.

case when a.PPCO_IND_MUTTOT=X'01' then 'J' else

case when a.PPCO_IND_MUTTOT=X'00' then 'N' else

a.PPCO_IND_MUTTOT end end

How do I convert this statement in de LOAD section in Qlickview?

The Null value is converted by this statement

If ( IsNull ( PPCO_IND_MUTTOT ) = -1 , 'NULL' , 'OTHER' ) AS PPCO_IND_MUTTOTM

Anyone knows how to convert de True and False values?

Thanx!

Michel

Report Inappropriate Content · ‎2013-10-24

Hello,

Well the problem is solved. Unit4 helped me solve this boolean load problem.

The select is changed as follows:

All the needed fields have been summed up instead of using a *.

And for the boolean fields the Case When is added.

PPCO_IND_MUTTOT,

CASE WHEN PPCO_IND_MUTTOT=X'01' THEN 'J' ELSE CASE WHEN PPCO_IND_MUTTOT=X'00' THEN 'N' ELSE 'NULL' END END AS PPCO_IND_MUTTOT,

In the load you also add the field PPCO_IND_MUTTOT even if this is a new field that does not exists in the source table and that looks like:

Load

PPCO_IND_MUTTOT,

Now it works fine.

Michel

View solution in original post

jagan · ‎2013-10-18

Hi,

When you are loading into Qlikview how the true/false values are retrieving, can you load this

SELECT DISTINCT PPCO_IND_MUTTOT

FROM Tablename;

based on the values try using If()

If(Value = 1/True, 'Y', If(Value = 0/False, 'N', 'N/A'))

Regards,

Jagan.

MayilVahanan · ‎2013-10-18

Hi

May be try like this

Pick(Match(PPCO_IND_MUTTOT,'01','00')+1,'Null', 'J','N') As PPCO_IND_MUTTOT

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

Report Inappropriate Content · ‎2013-10-18

Hi Mayil,

Thanx for the effort but only the Null has been recoqnised and not the true en a false.

Report Inappropriate Content · ‎2013-10-18

Hi Jagan,

Thanx for the suggestion but I get the next error message:

Field not found - <True>

SQL SELECT *

FROM PARTSPLAN.PPCO_CONDITIE

WHERE PPCO_EINDDATUM = '31-12-9999'

Any options?

jagan · ‎2013-10-18

Try this

If(PPCO_IND_MUTTOT= True(), 'Y', If(PPCO_IND_MUTTOT= False(), 'N', 'N/A'))

Regards,

Jagan.

Report Inappropriate Content · ‎2013-10-18

Hi Jagan,

This should be the full statement, but again it only results in Null and Other.

If(IsNull(PPCO_IND_MUTTOT) = -1,'NULL',If(PPCO_IND_MUTTOT= True(), 'JA', If(PPCO_IND_MUTTOT= False(), 'NEE', 'OTHER'))) AS PPCO_IND_MUTTOTM,

Thanx though

MayilVahanan · ‎2013-10-18

Hi

Can you provide a sample file?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

Report Inappropriate Content · ‎2013-10-18

Sample file is attatched.

If you select OTHER the you see, when you search the column right next to it (the original field from de db2 database), that 2 empty values are available, one being the true en one being false.

Hope it helps!

Michel

Report Inappropriate Content · ‎2013-10-18

It's not text but thanx for helping

sample file attached in reaction to Mayil