topic show only null values in QlikView

show only null values

Mon, 30 Mar 2015 06:41:18 GMT

Hi all,

I am new to qlikview, i want to show under managers how many employees skill not yet updated.

Can you Please any one help on these

manager	empid	skill
SHYAM	101	C
SHYAM	102	C++
RAM	1001	JAVA
RAM	1002
RAM	1003

Regards,

SRUTHI

Re: show only null values

PrashantSangle — Mon, 30 Mar 2015 06:46:46 GMT

Hi,

If want in script

Use Where clause

load * from table

where isnull(skill) or len(trim(skill))=0;

Regards

Re: show only null values

anbu1984 — Mon, 30 Mar 2015 06:50:41 GMT

Check this

Re: show only null values

Mon, 30 Mar 2015 06:53:49 GMT

HI Max,

Thanks for your reply, I want to show it in a straight table Like this

RAM	1002		USA
RAM	1003		USA

Regards,

Sruthi

Re: show only null values

Mon, 30 Mar 2015 07:09:46 GMT

Hi, what i need to check, i didn't find any attachment

Re: show only null values

PrashantSangle — Mon, 30 Mar 2015 07:13:51 GMT

Hi,

In straight table write expression,

if(isnull(skill) or len(trim(skill))=0,skill)

check supress null values from Presentation tab

Regards

Re: show only null values

Mon, 30 Mar 2015 07:14:59 GMT

try this in your script

LOAD * Inline [

manager,empid,skill

SHYAM,101,C

SHYAM,102,C++

RAM,1001,JAVA

RAM,1002,

RAM,1003,

]

Re: show only null values

Peter_Cammaert — Mon, 30 Mar 2015 07:21:21 GMT

Or create a flag without changing the actual skill data:

LOAD *, if (len(trim(skill)) = 0, 'N', 'Y') AS skill_flag Inline [

manager,empid,skill

SHYAM,101,C

SHYAM,102,C++

RAM,1001,JAVA

RAM,1002,

RAM,1003,

];

and use set analysis like this in your straight table expression(s):

...{<skill_flag={'N'}>}...

Re: show only null values

anbu1984 — Mon, 30 Mar 2015 07:36:43 GMT

Re-attaching QVW

Re: show only null values

Mon, 30 Mar 2015 07:46:56 GMT

HI anbu, I am having the data like this.

LOAD * Inline [

manager,empid,skill

SHYAM,101,C

SHYAM,102,C++

RAM,1001,JAVA

RAM,1002,-

RAM,1003,-]

Re: show only null values

Mon, 30 Mar 2015 07:59:49 GMT

RAM	1002	-
RAM	1003	-

I want to show like this in straight table,

Thank you.

Re: show only null values

PrashantSangle — Mon, 30 Mar 2015 08:03:07 GMT

Hi,

In straight table write expression,

if(isnull(skill) or len(trim(skill))=0 or match(skill,chr(45)),skill)

check supress null values from Presentation tab

Regards

Re: show only null values

richard_chilvers — Mon, 30 Mar 2015 08:29:09 GMT

Agreed, Peter.

This type of requirement with null values pops up all the time, and having tried several approaches, the one you suggest (where a null value is given a 'proper' value) seems to work best.

Re: show only null values

Mon, 30 Mar 2015 08:40:06 GMT

Hi Sruthi,

try this in your script

LOAD * INLINE [

manager, empid, skill

SHYAM, 101, C

SHYAM, 102, C++

RAM, 1001, JAVA

RAM, 1002

RAM, 1003

] where Len(Trim(skill))=0;

After this add fields in staright table will display only missing values of skills one.

Re: show only null values

Peter_Cammaert — Mon, 30 Mar 2015 08:51:43 GMT

There is an even better approach which may not apply to this case; very often, NULL values are unwanted and indicate data that shouldn't be missing at all. A translation technique like:

... if (len(trim(skill)) = 0, '**UNASSIGNED**', skill) AS skill, ...

will prove to be a lieutenants best drill-sergeant...

Re: show only null values

richard_chilvers — Mon, 30 Mar 2015 08:53:41 GMT

Yes, indeed. Been tripped up in that way a few times before now !

Re: show only null values

anbu1984 — Mon, 30 Mar 2015 09:03:07 GMT

Check this app

Re: show only null values

qlikview979 — Thu, 15 Jun 2017 05:33:22 GMT

Hi Sruthi,

May be this

T1:

LOAD * Inline [

manager,empid,skill

SHYAM,101,C

SHYAM,102,C++

RAM,1001,JAVA

RAM,1002,-

RAM,1003,-];

NoConcatenate

T2:

load

manager,empid,skill

Resident T1 where Match(skill,'-') ;

DROP Table T1;

In straight table

Dimension:-manager,empid

Expression:-skill

Re: show only null values

passionate — Thu, 15 Jun 2017 08:12:39 GMT

Hi Shruti,

Try Using this in your load script:

LOAD manager,

empid,

skill,

If(Len(skill)=0,'-',0) as NotSkilled

FROM

(ooxml, embedded labels, table is Sheet1);

In Straight Table use:

Dimension1: manager

Dimension2: EmpId

Expression : NotSkilled

Regards,

Pankaj

Re: show only null values

abc_18 — Thu, 15 Jun 2017 11:45:01 GMT

/*shows only Null values*/

temp:

LOAD manager,

empid,

If(Len(skill)=0,'-',0) as NoSkill

FROM

(ooxml, embedded labels, table is Sheet1);

/*show only nonnull values*/

temp:

LOAD manager,

empid,

If(IsNull(skill),0,skill) as Skilled

FROM

(ooxml, embedded labels, table is Sheet1);