IF(ISNULL... working for one field but not the other!

[matthewp]

I have four lines in my load script.

     [table1.ONumber] AS [O Number]

     if(isnull([table1.ONumber]),'Missing','Ok') AS [T1O Check],

and

     [table2.PNumber] AS [P Number]

     if(isnull([table2.PNumber]),'Missing','Ok') AS [T2P Check],

The first one returns data as it should

O Number     T1O Check

OO 123 IM    Ok

OO 435 IM    Ok

                    Missing

OO 358 IM    Ok

OO 329 IM    Ok

However the second one isn't treating the nulls as it should

P Number     T2P Check

PP 123 IM    Ok

PP 435 IM    Ok

PP 358 IM    Ok

PP 329 IM    Ok

The code works fine if used in an expression in a straight table chart, just not in my load script.

[matthewp]

Ok so all the "OK" ones are showing as 67 and the missing ones are just showing as null ( - )

[trdandamudi]

Can you please post a small sample file to test. Also what is the Qlikview version you are using?

[matthewp]

QV version 12

[sunny_talwar]

May be use this to our advantage

If(Len(trim(Ord([table2.PNumber]))) > 0, 'OK', 'Missing') as [T2P Check],

[matthewp]

Still returning nulls

[sunny_talwar]

I think the dimension you might be using in your front end object where it is working is probably from table1? Are you joining table1 and table2 after you create ok, missing flags? The reason i ask this is because, may be there is not missing values, but they get created by doing a join.

I think it would help if you can share your script.

[trdandamudi]

If possible please share a sample .qvw so that we can test, I am using version 11. Just want to see if it has anything to do with the version.

[matthewp]

yes there is a join.

table1:

LOAD

     [table1.ONumber] AS [O Number],

     if(isnull([table1.ONumber]),'Missing','Ok') AS [T1O Check]

FROM

C:\Users\abc\Desktop\table1.QVD

(qvd);

LEFT JOIN(table1)

IFS:

LOAD

     [table2.PNumber] AS [P Number],

     if(isnull([table2.PNumber]),'Missing','Ok') AS [T2P Check]

     //if(ISNULL([table2.PNumber]),'Missing','Ok') AS [T2P Check]

     // if(ISNULL([table2.PNumber]) OR TRIM([table2.PNumber])='','Missing','Ok') AS [T2P Check]

     //if(LEN(TRIM(ORD([table2.PNumber])))<> 0,'Missing','Ok') AS [T2P Check]

FROM

C:\Users\abc\Desktop\table2.QVD

(qvd);

resulttable:

NoConcatenate

load *

resident table1;

drop table table1;

[sunny_talwar · Accepted solution]

Try this:

Table1:

LOAD

     [table1.ONumber] AS [O Number],

     if(isnull([table1.ONumber]),'Missing','Ok') AS [T1O Check]

FROM

C:\Users\abc\Desktop\table1.qvd (qvd);

Left Join (Table1)

LOAD [table2.PNumber] AS [P Number]

FROM

C:\Users\abc\Desktop\table2.qvd (qvd);

ResultTable:

NoConcatenate

LOAD *,

          if(isnull([P Number]),'Missing','Ok') AS [T2P Check]

Resident Table1;

DROP Table Table1;