Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
roee1983
Contributor III
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
PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
roee1983
Contributor III
Contributor III
Author

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

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
Master III
Master III

Can you post expected output