topic Re: ISNULL Function in QlikView

ISNULL Function

roee1983 — Thu, 23 Apr 2015 05:58:42 GMT

Hi all,

I need to have a check which return if a value is null or not.

if it is null, need to get sum of different values.

if the value exists so we just need to bring it.

attached is the table with example :

T1:

* INLINE [

    Mapping, Data

    A, 10

    B, 20

    C, 30

    E, 40

    F, 50

    G, 60



];

LOAD

///////////// ************************************************ check if c exists ( c= a+b)

IF (ISNULL(WildMatch(Mapping ,'C'))= 0 ,0, SUM(Data)) AS C,

Mapping

resident T1

Where 1 AND

WildMatch(Mapping,'A','B','C')

Group BY

Mapping ;

LOAD

///////////// *********************************************** check if H exists ( H= a+b+f+g)

IF (ISNULL(WildMatch(Mapping ,'H'))= 0 ,0, SUM(Data)) AS H,

Mapping

resident T1

Where 1 AND
WildMatch(Mapping,'H','B','F','G')

Group BY

Mapping ;

Thanks

Roee

Re: ISNULL Function

PrashantSangle — Thu, 23 Apr 2015 06:03:18 GMT

Hi,

You can use isnull(fielName) or len(trim(fieldName))=0

for example:

If(isnull(fieldName),AnotherFieldName,FieldNAme) as New_fielName

Regards

Re: ISNULL Function

roee1983 — Thu, 23 Apr 2015 06:13:08 GMT

Hi Max,

thank you for your quick answer .

I tried and it doesn't work.

LOAD

if(len(trim(WildMatch(Mapping,'H')))= 0,sum(Data)) AS XXX,

Mapping

resident T1

Where 1 AND
WildMatch(Mapping,'H','B','F','G')

Group BY

Mapping ;

Re: ISNULL Function

Thu, 23 Apr 2015 07:57:18 GMT

As per my understanding, please find the solution below:

data0:

LOAD * INLINE [

Mapping, MapID

A, 1

B, 2

C, 3

D, 4

E, 5

F, 6

G, 7

H, 8

];

data1:

Left join(data0)

LOAD * INLINE [

Mapping, Data

A, 10

B, 20

C, 30

E, 40

F, 50

G, 60

]

where Exists(Mapping);

data3:

LOAD

MapID,

Mapping,

Data,

NUMSUM(PEEK('_tmp_Data'),Data) AS _tmp_Data

RESIDENT data0 ORDER BY MapID;

DROP TABLE data0;

final:

LOAD

MapID,

Mapping,

Data,

_tmp_Data,

IF(IsNull(Data),_tmp_Data,Data) As FinalData

RESIDENT data3 ;

DROP TABLE data3;

//Use: Mapping , FinalData as fields in your tablebox

Thanks,

Angad

Re: ISNULL Function

anbu1984 — Thu, 23 Apr 2015 08:33:24 GMT

Can you post expected output