Loading all values except null in a field

[#1]

This should be a simple question, but I cannot get it to work, so I'm asking for hellp. In SQL Developer, when I create my query as this:

select * from APPS.xeta_cust_service_info_v

where contract_number is not null

order by contract_number desc;

My question is: What is the syntax on the WHERE clause in Qlikview, in order to pull only the contract numbers that are not null? Obviously, the same syntax doesn't work, or I wouldn't be asking this question.

Thank you for your help!

[#2]

WHERE NOT ISNULL(contract_number) should work

ISNULL(contract_number) returns -1 if contract_number is null and 0 if it is not null.  So alternatively you could use:

WHERE ISNULL(contract_number) = 0

But the first is closer to what you are used to in SQL.

Hope this helps!

-Brandon

[#3]

Thank you sir!

[#4]

Okay, wait, I tried it and it still doesn't work. Here is my script load:

LOAD "CUSTOMER_NUMBER",

    "CUSTOMER_NAME" as Service_Customer_Name,

    "XETA_#",

    NUM("SYSTEM_ID") as SYSTEM_ID,

    1 as System_counter,

    "SYSTEM_NAME",

//    ADDRESS1,

//    ADDRESS2,

//    CITY,

//    STATE,

//    "POSTAL_CODE",

//    "PHONE_COUNTRY_CODE",

//    "PHONE_NUMBER",

//    "FAX_NUMBER",

    NUM("CONTRACT_NUMBER") as CONTRACT_NUMBER,

    "CONTRACT_DESCRIPTION",

    "CONTRACT_TYPE",

    "CONTRACT_START_DATE",

    Month("CONTRACT_START_DATE") as CONTRACT_ST_MONTH,

    Year("CONTRACT_START_DATE") as CONTRACT_ST_YEAR,   

    "CONTRACT_END_DATE",

    Month("CONTRACT_START_DATE") as CONTRACT_END_MONTH,

    Year("CONTRACT_START_DATE") as CONTRACT_END_YEAR,

    "ORIGINAL_START_DATE",

    "COVERAGE_LEVEL",

    "MODEM1#",

    EXTENSION1,

    "CUSTOM_SOFTWARE",

    "LAST_UPDATE",

    "NEXT_UPDATE",

    "UPD_CONTACT_NAME",

    "UPD_AUTH_BY",

    "TYPE_OF_AUTH",

    "DATABASE_TECH",

    "TERMINATED_DATE",

    "MANAGEMENT_COMPANY",

    DISTRIBUTOR,

    AFFILIATE1,

    AFFILIATE2,

    AFFILIATE3,

    AFFILIATE4,

    "SALES_PERSON_NAME",

    "CREDIT_HOLD_YN";

SQL SELECT *

FROM APPS."XETA_CUST_SERVICE_INFO_V"

Where NOT ISNULL(CONTRACT_NUMBER);

[#5]

In this case since you are using SQL I would think that you would need to use SQL syntax rather than QlikView:

SQL SELECT *

FROM APPS."XETA_CUST_SERVICE_INFO_V"

Where "CONTRACT_NUMBER" IS NOT NULL;

Does that not work?

Otherwise, as a slightly more ineffecient solution that I'm pretty sure should work.  You could always load it into a table first:

Table1:

LOAD "CUSTOMER_NUMBER",

    "CUSTOMER_NAME" as Service_Customer_Name,

    "XETA_#",

    NUM("SYSTEM_ID") as SYSTEM_ID,

    1 as System_counter,

    "SYSTEM_NAME",

//    ADDRESS1,

//    ADDRESS2,

//    CITY,

//    STATE,

//    "POSTAL_CODE",

//    "PHONE_COUNTRY_CODE",

//    "PHONE_NUMBER",

//    "FAX_NUMBER",

    NUM("CONTRACT_NUMBER") as CONTRACT_NUMBER,

    "CONTRACT_DESCRIPTION",

    "CONTRACT_TYPE",

    "CONTRACT_START_DATE",

    Month("CONTRACT_START_DATE") as CONTRACT_ST_MONTH,

    Year("CONTRACT_START_DATE") as CONTRACT_ST_YEAR,  

    "CONTRACT_END_DATE",

    Month("CONTRACT_START_DATE") as CONTRACT_END_MONTH,

    Year("CONTRACT_START_DATE") as CONTRACT_END_YEAR,

    "ORIGINAL_START_DATE",

    "COVERAGE_LEVEL",

    "MODEM1#",

    EXTENSION1,

    "CUSTOM_SOFTWARE",

    "LAST_UPDATE",

    "NEXT_UPDATE",

    "UPD_CONTACT_NAME",

    "UPD_AUTH_BY",

    "TYPE_OF_AUTH",

    "DATABASE_TECH",

    "TERMINATED_DATE",

    "MANAGEMENT_COMPANY",

    DISTRIBUTOR,

    AFFILIATE1,

    AFFILIATE2,

    AFFILIATE3,

    AFFILIATE4,

    "SALES_PERSON_NAME",

    "CREDIT_HOLD_YN";

SQL SELECT *

FROM APPS."XETA_CUST_SERVICE_INFO_V"

;

Table2:

LOAD *

RESIDENT Table1

WHERE NOT ISNULL(CONTRACT_NUMBER);

drop table Table1;

The resulting set will appear in Table2

[#6]

You may try LEN(TRIM(CONTRACT_NUMBER))>0

Thanks.

[#7]

Hi Dlee, try this:

LOAD "CUSTOMER_NUMBER",

    "CUSTOMER_NAME" as Service_Customer_Name,

    "XETA_#",

    NUM("SYSTEM_ID") as SYSTEM_ID,

    1 as System_counter,

    "SYSTEM_NAME",

//    ADDRESS1,

//    ADDRESS2,

//    CITY,

//    STATE,

//    "POSTAL_CODE",

//    "PHONE_COUNTRY_CODE",

//    "PHONE_NUMBER",

//    "FAX_NUMBER",

    NUM("CONTRACT_NUMBER") as CONTRACT_NUMBER,

    "CONTRACT_DESCRIPTION",

    "CONTRACT_TYPE",

    "CONTRACT_START_DATE",

    Month("CONTRACT_START_DATE") as CONTRACT_ST_MONTH,

    Year("CONTRACT_START_DATE") as CONTRACT_ST_YEAR,  

    "CONTRACT_END_DATE",

    Month("CONTRACT_START_DATE") as CONTRACT_END_MONTH,

    Year("CONTRACT_START_DATE") as CONTRACT_END_YEAR,

    "ORIGINAL_START_DATE",

    "COVERAGE_LEVEL",

    "MODEM1#",

    EXTENSION1,

    "CUSTOM_SOFTWARE",

    "LAST_UPDATE",

    "NEXT_UPDATE",

    "UPD_CONTACT_NAME",

    "UPD_AUTH_BY",

    "TYPE_OF_AUTH",

    "DATABASE_TECH",

    "TERMINATED_DATE",

    "MANAGEMENT_COMPANY",

    DISTRIBUTOR,

    AFFILIATE1,

    AFFILIATE2,

    AFFILIATE3,

    AFFILIATE4,

    "SALES_PERSON_NAME",

    "CREDIT_HOLD_YN";

SQL SELECT *

FROM APPS."XETA_CUST_SERVICE_INFO_V"

Where ISNULL(CONTRACT_NUMBER)<>-1;

Because isNull() function returns -1 if the value is null!

I hope that it help you, if not just tell me or attach your *.qvw

Regards

Agustin

[PradeepReddy]

While fetching the data from SQL DB,  the SQL statement will work in QlikView as well...

try some thing like this

Temp:

LOAD "CUSTOMER_NUMBER",

    "CUSTOMER_NAME" as Service_Customer_Name,

    "XETA_#",

    NUM("SYSTEM_ID") as SYSTEM_ID,

    1 as System_counter,

    ....;

SQL select *

from APPS.xeta_cust_service_info_v

where contract_number is not null

order by contract_number desc;