invalid expression for if condition for Null value

[43918084]

I have below script that has no problem but ecounter Invalid expression once I add null condition.

Would appreciate experte guidance to make the correction.  Many thanks.

No issue in below script

load Distinct
key,
sum(timeSpentSeconds) as timeSpentSeconds,

if((sum([timeSpentSeconds])/60/60/8)>1,round(sum([timeSpentSeconds])/60/60/8)&'d', frAC(sum([timeSpentSeconds])/60/60/8)*8&'h')as [timeSpent_day_logged]
resident Worklog
group by key;

Drop table Worklog;

Invalid expression in below script

load Distinct
key,
sum(timeSpentSeconds) as timeSpentSeconds,

if(isnull([timeSpentSeconds]),'not specified',(if((sum([timeSpentSeconds])/60/60/8)>1,round(sum([timeSpentSeconds])/60/60/8)&'d', frAC(sum([timeSpentSeconds])/60/60/8)*8&'h')))

as [timeSpent_day_logged]
resident Worklog
group by key;

Drop table Worklog;

[pravinboniface]

It looks like Qlik is expecting an aggregate function inside your IsNull() as this is part of a Group By clause.  When I add a Sum() to your statement, it works, but please test to make sure you are getting the results you expect.

if(isnull(sum([timeSpentSeconds])),'not specified',

(if((sum([timeSpentSeconds])/60/60/8)>1,round(sum([timeSpentSeconds])/60/60/8)&'d', frAC(sum([timeSpentSeconds])/60/60/8)*8&'h'))

  ) as [timeSpent_day_logged]

[43918084]

Thanks a lot for your expertise.  The invalid expression is fixed but those with "-" does not return as "not Specified".  Would appreciate if you can guide me further.  Many thanks again.

[nimishpgupta]

When you are using groupby there is a rule that all fields that are not agrregated should be included in groupby clause, here in your second load statement you are using timespentseconds which was only used to aggregate in your first load statement.

[43918084]

Thanks a lot. Apology that I am new to coding and not quite understand your advice.

Hope you do not mind to let me know how to correct it.

[pravinboniface]

How about something like this:

if(sum(len([timeSpentSeconds]))=0,'not specified',

(if((sum([timeSpentSeconds])/60/60/8)>1,round(sum([timeSpentSeconds])/60/60/8)&'d', frAC(sum([timeSpentSeconds])/60/60/8)*8&'h')))

as [timeSpent_day_logged]

[pravinboniface]

Here's another option below.  Please note that when you have both nulls and non-nulls for a key, then the nulls will be treated as zero in your sum same as in the earlier example.

if(count([timeSpentSeconds])=0,'not specified',

(if((sum([timeSpentSeconds])/60/60/8)>1,round(sum([timeSpentSeconds])/60/60/8)&'d', frAC(sum([timeSpentSeconds])/60/60/8)*8&'h')))

as [timeSpent_day_logged]

[nimishpgupta]

You can modify the field while loading data using resident load

ModifiedWorklog:

Load

If(isnull(timeSpentSeconds),0,timeSpentSeconds)

Resident Worklog;

And then use this modified field instead.

Their is another option to use NullAsValue in script.

NullAsValue timeSpentSeconds;

Set NULLVALUE = 'NULL';

[43918084]

Apology for the trouble to both expert.  The suggested solutions did not work.  I still get "-" for those missing value.  I wonder if there is different approach for fields that is null vs no input?

Many thanks again

[Kushal_Chawda]

@43918084  try using one more load after grouping

step:
load Distinct
key,
sum(timeSpentSeconds) as timeSpentSeconds,
resident Worklog
group by key;

Drop table Worklog;

Final:
LOAD *,
    if(len(trim([timeSpentSeconds]))=0,'not specified',  if([timeSpentSeconds]/60/60/8>1,round([timeSpentSeconds]/60/60/8)&'d', frAC([timeSpentSeconds]/60/60/8)*8&'h'))
     as [timeSpent_day_logged]

resident step;

drop table step;