Use Is blank formula from excel to collate data in Qlikview

[#1]

Hi All,

Is there a way I can use a IsBlank formula in Qlikview to collate data in Qlikview

The current data in excel uses the calculation formula as

=IF(ISBLANK([@[Accepted_age]]),IF([@[Reason]] = "Expired","Expired","Not Accepted"),IF([@[Accepted_age]]>[@[Accept_M]],"No","Yes"))

Is this I can achieve something in Qlikview so that the columns can calculate the data accordingly?

Need help on this urgently would really be great.

Regards,

Prathamesh Sable

[#2]

You can try IsNull() in QlikView

[#3]

Hi,

I'm not an excel expert but If isBlank() function is a bolean function that return 1 if is null and 0 if not is null a field, you can use isnull() function in qlikview or a convination of 2 functions ( Len(Trim(MyField))=0 ) to know if some field is null.

Using your example, your formula will be like this:

Option 1 (Using isnull()):

IF(isNUll(Accepted_age),

     IF(Reason='Expired',

          'Expired',

          'Not Accepted'

     ),

     IF(Accepted_age>AcceptM,

          'No',

          'Yes'

     )

)

or

Option 2 (Using Len(Trim(MyField))=0 😞

IF(Len(Trim(Accepted_age)) = 0,

  IF(Reason='Expired',

          'Expired',

          'Not Accepted'

     ),

     IF(Accepted_age>AcceptM,

          'No',

          'Yes'

     )

)

Best regards

[aveeeeeee7en]

Hi

Try something like this:

=IF(((Len(TRIM([@[Accepted_age]]))=0)) AND (Match([@[Reason]],'Expired','Expired','NotAccepted'))

AND (([@[Accepted_age]]>[@[Accept_M]])),'No','Yes')

OR,

Not sure about this:

=IF(((Len(TRIM([@[Accepted_age]]))=0)),if((Match([@[Reason]],'Expired','Expired','NotAccepted')),

if(,(([@[Accepted_age]]>[@[Accept_M]])),'No','Yes')))

Regards

Aviral Nag

[#5]

Hi,

How can i create these calculations into a new column/dimension when i load the data? because I need to use this formula to calculate a new column in Qlikview.

Regards,

Prathamesh Sable

[#6]

Hi Oswaldo,

Is there a way I can derive separate expressions there which would be easier to calculate? I would want a greater  than equal to expression which would derive the sum of total

You can refer to my another post as well

http://community.qlik.com/message/586430?et=watches.email.thread#586430

Regards,

Prathamesh Sable

[#7]

Hi,

How can i create these calculations into a new column/dimension when i load the data? because I need to use this formula to calculate a new columns in Qlikview.

Regards,

Prathamesh Sable

[#8]

Hi Aviral,

Thanks for the reply..How can I use this in a new dimension in Qlikview in the script?

Regards,

Prathamesh Sable

[#9]

Hi,

You can create this calculated field in Script editor or you can use it in a chart in Dimension Sheet click on "Add Calculated Dimension" and just tip your formula.

Best regards.

[#10]

Hi,

Yes you can calculate in script two separate field and then use it in your charts. Something like this:

//Script Editor:

LOAD

     Field1,

     Field2,

     FieldN,

     IF(isNUll(Accepted_age),

          IF(Reason='Expired',

               'Expired',

               'Not Accepted'

          )

     ) as Expression1,

     IF(Accepted_age>AcceptM,

          'No',

          'Yes'

     ) as Expression2

From mydata.qvd;

// Then in a chart, expression sheet:

IF( IsNull(Accepted_Age),

     Expression1, // The field that you created in script editor

     Expression2, // The field that you created in script editor

)

And that's all.

Best regards.