topic Re: Report record with blank date in QlikView

Report record with blank date

tracycrown — Sat, 16 Jan 2016 03:40:02 GMT

Dear all

I am trying to generate a report containing records with blank ReqDate (ie. last two records) using if(ReqDate='',SO#) in dimensions but it does not work, please advise.

SO#	OrdDate	ReqDate	OrdAmt
1001	09/01/2014	14/01/2014	200
1002	13/03/2014	22/03/2014	300
1003	24/04/2014	28/04/2014	1,200
1004	24/06/2014	200
1005	24/06/2014	400

Thank you so much

Tracy

Re: Report record with blank date

sunny_talwar — Sat, 16 Jan 2016 04:11:55 GMT

Try this as the calculated dimension:

=if(Len(Trim(ReqDate)) = 0,SO#)

Re: Report record with blank date

sunny_talwar — Sat, 16 Jan 2016 04:18:52 GMT

Although a more efficient method would be to use a set analysis expression:

Sum({<SO# = {"=Len(Trim(ReqDate)) = 0"}>}OrdAmt)

With SO#, OrdDate, ReqDate as Dimensions

Re: Report record with blank date

Sat, 16 Jan 2016 04:51:13 GMT

Tracy, add the Flag field on the table at script level.

Load

[SO#],

OrderDate,

RequestDate,

Amount,

IF(Len(Trim(RequestDate)),1,0) as IsRequestDateBlank

From SourceTable

;

on UI, create calculated Dimension or Set expression to show blank request date

Calculated Diemension:

IF(IsRequestDateBlank=0,[SO#],null() and Select Suppress when Dimension Value is null on the dimension tab.

Set Expression: Sum({<IsRequestDateBlank={0}>}Amount)

You really don't need a Flag field but if we have huge data set Flag with set analysis may give better performance.