Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Set analysis to exclude a record when a date field is blank

I have 3 fields

Actualdate

Currentdelinquent

scheduleddate

At the moment I have the following expression that excludes all of the records where the currentdelinquent field is = null

      If(not isnull([completeddelinquent]),actualdate )

This works but it also returns records where actual date is = to blank and I also want to exclude these records.

I think I need to use set analysis combined with the formula above but can't get it to work

18 Replies
stigchel
Honored Contributor

Re: Set analysis to exclude a record when a date field is blank

Try with

Only({<[completeddelinquent]={'*'}>} actualdate)

gautik92
Valued Contributor III

Re: Set analysis to exclude a record when a date field is blank

try e() you may get t

or try this

  only( If(not isnull([completeddelinquent]),actualdate ) )

Not applicable

Re: Set analysis to exclude a record when a date field is blank

Hi

Thanks  but that didn't work it left the blanks for actualdate and bought in the null fields for currendelinquent that I was excluding

Not applicable

Re: Set analysis to exclude a record when a date field is blank

Hi

Thanks  but that didn't work it left the blanks for actualdate and bought in the null fields for currendelinquent that I was excluding

MVP
MVP

Re: Set analysis to exclude a record when a date field is blank

HI,

Try like this

TableName:

LOAD

Actualdate,

Currentdelinquent,

scheduleddate,

If(Len(Trim(Currentdelinquent)) = 0, 1, 0) AS  IsNull

FROM Datasource;

Now in front end you can use

Only({<IsNull={0}>}actualdate )

OR you can try this without any changes in script

If(not Len(Trim(([completeddelinquent])) = 0,actualdate )


Regards,

Jagan.


MVP
MVP

Re: Set analysis to exclude a record when a date field is blank

OR simply

If(Len(Trim(([completeddelinquent])) > 0,actualdate )


REgards,

Jagan.

Not applicable

Re: Set analysis to exclude a record when a date field is blank

Thanks

This didn't work, it still bought in where the actual date is blank

MVP
MVP

Re: Set analysis to exclude a record when a date field is blank

Hi,

Are you checking Blank for Actual date?  Then use this

If(Len(Trim(([actualdate ])) > 0,actualdate )

OR

If(Len(Trim(([actualdate ])) > 0, DimensionName)


antoniotiman
Honored Contributor III

Re: Set analysis to exclude a record when a date field is blank

Perhaps are You loading data from MySql ?

Community Browser