Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

roee1983
New Contributor III

ISNULL Function

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

4 Replies

Re: ISNULL Function

Hi,

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

for example:

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
roee1983
New Contributor III

Re: ISNULL Function

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 ;


Not applicable

Re: ISNULL Function

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

anbu1984
Honored Contributor III

Re: ISNULL Function

Can you post expected output