topic Re: How to show Null Values in QlikView

How to show Null Values

Thu, 04 Dec 2014 07:20:49 GMT

Hi Everyone,

My first post to this community.

I have two tables both are joined with EmpId but the values are not same in both the fields.

Suppose in 1 table I have some null values in EmpId (Doesn't have any value)and some unmatched values with the second table's EmpId.

(Those are not matched with the EmpId of 2nd table)

I want to have two fields where I can show the EmpId which is null and in another field I want to show the unmatched EmpId.

Please suggest.

Thanks

Subhrajit

Re: How to show Null Values

amit_saini — Thu, 04 Dec 2014 07:27:53 GMT

Check this:

NULL handling in QlikView

Thanks,
AS

Re: How to show Null Values

Anonymous — Thu, 04 Dec 2014 10:19:58 GMT

after joining the tables

perform a resident load and apply where condition depending on the reqirement

where len(field_to_check)>0 for non empty values

(or)

where len(field_to_check)=0 for empty values

Regards

Harsha

Re: How to show Null Values

Anonymous — Thu, 04 Dec 2014 11:07:20 GMT

Hi,

Please see below solution for your requirements:

Copy below code in Script, which as a field for Nulls and a field for unmatched Records:

//-- First table
Employee1:
LOAD EmpID as EmpID1

Name

Age
FROM Employee1

//-- Second table
Employee2:
LOAD EmpID as EmpID2

Address

City

State
FROM Employee2

//-- All records from both tables
All:
Load *, EmpID1 as EmpID,

if(Len(Trim(EmpID1))= '0',1,0) as CountID

Resident Employee1;

concatenate (All)

Load *,EmpID2 as EmpID,

if(Len(Trim(EmpID2))= '0',1,0) as CountID

Resident Employee2;

//-- Common records
Match:
load EmpID as match
Resident All
where exists(EmpID1,EmpID) and exists(EmpID2,EmpID);

//-- Unmatching records
Unmatch:
Load EmpID as unmatch
Resident All
where not exists(match,EmpID);

Copy below code in UI, which gives count of Nulls:

=count({<CountID ={'0'}>} EmpID)

Regards

Neetha

Re: How to show Null Values

Thu, 04 Dec 2014 12:41:22 GMT

Hi Neetha,

Many thanks for your help..

I copied the whole script and tried.

But What I think it is comparing with all the EmpIds and then it is showing matched and Unmatched status.

But I want distinct values to be displayed.

PFA the attached screenshot.

Please adivise.

Re: How to show Null Values

Thu, 04 Dec 2014 13:41:58 GMT

Hi ,

Tab1:

Empid	Name
101	irf
102	gho
	zee

Tab2 :

Empid	Name
101	irf
102	gho
103	zee

Script 1:

Tab1:

LOAD

Empid as Empid1,

Name

FROM

(ooxml, embedded labels, table is Tab1);

outer Join

LOAD

Empid as Empid2,

Name

FROM

(ooxml, embedded labels, table is Tab2);

Script2:

Tab1:

LOAD Empid,

Empid as Empid1,

Name

FROM

(ooxml, embedded labels, table is Tab1);

outer Join

Tab2:

LOAD Empid,

Empid as Empid2,

Name

FROM

(ooxml, embedded labels, table is Tab2)

;

Result :

Script 1:

Empid1	Empid2	Name
101	101	irf
102	102	gho
	103	zee

Script 2:

Empid1	Empid2	Name
101	101	irf
102	102	gho
	103	zee
		zee

Re: How to show Null Values

Anonymous — Thu, 04 Dec 2014 14:06:56 GMT

hi,

Please try below script:

//-- First table
Employee1:
LOAD EmpID,
Name,
Age
FROM Employee1;

//-- Second table
Employee2:
LOAD EmpID,
Address,
City,
State
FROM Employee2;

//-- All records from both tables
All:
Load *, EmpID as EmpID1,
if(Len(Trim(EmpID))= '0',1,0) as CountID
Resident Employee1;

concatenate (All)

Load *,EmpID as EmpID2,
if(Len(Trim(EmpID))= '0',1,0) as CountID
Resident Employee2;

//-- Common records
Match:
load EmpID as match
Resident All
where exists(EmpID1,EmpID) and exists(EmpID2,EmpID);

//-- Unmatching records
Unmatch:
Load EmpID as unmatch
Resident All
where not exists(match,EmpID);

Regards

Neetha