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

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

Mon, 22 Jun 2015 10:01:08 GMT

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

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

stigchel — Mon, 22 Jun 2015 10:04:41 GMT

Try with

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

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

gautik92 — Mon, 22 Jun 2015 10:08:03 GMT

try e() you may get t

or try this

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

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

Mon, 22 Jun 2015 10:10:06 GMT

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

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

Mon, 22 Jun 2015 10:10:20 GMT

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

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

jagan — Mon, 22 Jun 2015 10:11:42 GMT

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.

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

jagan — Mon, 22 Jun 2015 10:12:21 GMT

OR simply

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

REgards,

Jagan.

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

Mon, 22 Jun 2015 10:25:01 GMT

Thanks

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

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

jagan — Mon, 22 Jun 2015 10:31:53 GMT

Hi,

Are you checking Blank for Actual date? Then use this

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

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

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

antoniotiman — Mon, 22 Jun 2015 10:38:08 GMT

Perhaps are You loading data from MySql ?

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

Mon, 22 Jun 2015 10:38:26 GMT

I now have this

if(not isnull([completeddelinquent]) and Len(Trim(([actualdate]))) > 0,actualdate)

but I get the same result and the actual date where is was blank now returns null, I need to exclude the whole record if actual date is blank

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

sasiparupudi1 — Mon, 22 Jun 2015 10:58:31 GMT

Please paste your script here

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

jagan — Mon, 22 Jun 2015 10:59:12 GMT

Hi,

Attach sample file so that it would be easier to provide the solution.

Regards,

Jagan.

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

Mon, 22 Jun 2015 11:01:28 GMT

// To create view options in the dashboard

viewTable:
Load * Inline [
tableView
All Milestones
Future Milestones
5 Week Overview
Prev. Wk Cust Milestones
All Customer Milestones
All Milestones Delinquent
Current Milestones Delinquent
Completed Milestones Delinquent
Projected Future Delinquencies
];

// Project header data
PROJHEADER:
LOAD
PSPID as WBS,
Text(Left(PSPID,2) & '.' & Mid(PSPID,3,6)) As FormattedNumber,
left(PSPID,8) as WBSkey,
POST1 as projdes,
VBUKR as companycode,
WERKS as plant,
WWMKT as market

FROM

(qvd)

Where Not VBUKR = '0001';

// Required to bring in region
left join
Region:
LOAD CmpID as companycode,
Region
FROM

(biff, embedded labels, table is Company$);

// Project detail data
PROJDETAIL2:
LOAD PSPNR as projectno,
POSID as wbsno1,
//Text(Left(POSID,2) & '.' & Mid(POSID,3,6) & '.' & Mid(POSID,9,2) & '.' & Mid(POSID,11,2) & '.' & Mid(POSID,13,2) & '.' & Mid(POSID,15,2)) as FormattedNumber1,
Text(Left(POSID,2) & '.' & Mid(POSID,3,6) & If(Mid(POSID, 9,2) <> '0', '.' & Mid(POSID,9,2), '') & If(Mid(POSID, 11,2) <> '0', '.' & Mid(POSID,11,2), '') & If(Mid(POSID, 13,2) <> '0', '.' & Mid(POSID,13,2), '') & If(Mid(POSID, 15,2) <> '0', '.' & Mid(POSID,15,2), ''))as FormattedNumber1,
left(POSID,8) as WBSkey,
left(POSID,2) as ProjectID,
POST1 as wbsdesc,
OBJNR as objectno,
PSPHI as currprojno,
VERNA as projectmanager,
STUFE as projectlevel

FROM

(qvd)

Where not left(POSID,2)> '90';

// Exclusion of closed projects
Left Join (PROJDETAIL2) LOAD
OBJNR as objectno,
STAT AS Projectstatus,
'X' as IGNORE2
FROM

(qvd) where STAT = 'I0046';

left join (PROJDETAIL2) LOAD FormattedNumber1,
only('X') as IGNORE
resident PROJDETAIL2 where IGNORE2 = 'X' group by FormattedNumber1;

NoConcatenate
PROJDETAIL:
load * RESIDENT PROJDETAIL2
Where IGNORE <> 'X';

drop table PROJDETAIL2;

// Operation number to link WBS with Milestone number
AFVC:
LOAD AUFPL as operationno,
PROJN as projectno,
AUFPL+APLZL as opno

FROM

(qvd)

where PROJN > 0;

// Milestone data
left join
MLST:
LOAD AUFPL as operationno,
APLZL as counter,
AUFPL+APLZL as opno,
MLST_ZAEHL as milestoneno,
num (MLST_ZAEHL) as milestone,
MLSTN as usage,
TEDAT as scheduleddate,
LST_ACTDT as actualdate,
if((TEDAT>today()),TEDAT) as FutureMilestones,
if(TEDAT>Today()and TEDAT <Today()+35,TEDAT)as '5WeekOverview',
if(TEDAT>=WeekStart(today(),0,-1)-7 and TEDAT<=WeekStart(today(),0,-1),TEDAT)as PrevWk,
if(TEDAT < Today() and LST_ACTDT = '',TEDAT) as Alldelinquent,
if(TEDAT < Today() and LST_ACTDT = '' and TEDAT >today()-304,TEDAT) as Currentdelinquent

FROM

(qvd)

where MLSTN = 'N0003';

// Milestone customer baseline data
left join
LOAD ZZMLST_ZAEHL1 as milestone,
ZZMLST_TEDAT1 as customerbaseline

FROM

(qvd);

Calctable:

NoConcatenate

// Creation of additional calculated fields
load
customerbaseline,
if(actualdate > ('') and actualdate > customerbaseline and scheduleddate > customerbaseline and customerbaseline > '' and actualdate > (''),scheduleddate) as completeddelinquent,
if(actualdate = ('') and scheduleddate > customerbaseline, scheduleddate) as projectdelinquent
Resident AFVC;

// Milestone descriptions
MLTX:
LOAD MLTX_ZAEHL as milestoneno,
KTEXT as milestonedesc,
Right(KTEXT,6) as Cust,
if(Right(KTEXT,6)='(Cust)' or Right(KTEXT,6)='(cust)','Customer','Internal') as Type

FROM

(qvd);

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

Mon, 22 Jun 2015 11:02:57 GMT

file added

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

sasiparupudi1 — Mon, 22 Jun 2015 12:24:50 GMT

Since you want to exclude all blank or null actualdate values,could may be use

where len(trim(actualdate))>0;

after the resident load Resident AFVC

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

Mon, 22 Jun 2015 12:28:51 GMT

Thanks, I need to deal with this in the expressions as I need all the records to create different views

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

sasiparupudi1 — Mon, 22 Jun 2015 13:45:20 GMT

Try changing the Companycode dimension to a calculated one with the condition

=if(actualdate<>'',companycode)

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

sasiparupudi1 — Fri, 03 Jul 2015 09:40:20 GMT

Is this still an issue?

Sasi