topic Loading all values except null in a field in QlikView

Loading all values except null in a field

Fri, 09 Mar 2012 15:38:12 GMT

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!

Loading all values except null in a field

Fri, 09 Mar 2012 17:01:29 GMT

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

Loading all values except null in a field

Fri, 09 Mar 2012 17:06:24 GMT

Thank you sir!

Loading all values except null in a field

Fri, 09 Mar 2012 17:14:58 GMT

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

Loading all values except null in a field

Fri, 09 Mar 2012 17:27:47 GMT

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

Re: Loading all values except null in a field

Tue, 15 Dec 2015 12:57:51 GMT

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

Thanks.

Re: Loading all values except null in a field

Tue, 15 Dec 2015 13:03:48 GMT

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

Re: Loading all values except null in a field

PradeepReddy — Tue, 15 Dec 2015 13:31:08 GMT

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;