set analysis with null value not working.

[berryandcherry6]

HI,

I am have requirement where i need to get sum of landing_hits where campaign_id = null and client_id = 1.

For this, i have

sum({($ <campaign_id ={'*'}>) * <client_id ={'1'}>} landing_hits)

For this it shows me zero value. But i do have data for this.

Regards,

Supriya

[shraddha_g]

Try

sum({<campaign_id ={'null()'},client_id ={'1'}>} landing_hits)

[jeevays7]

Hi Supriya,

try this,

Sum({$<campaign_id ={''},client_id={'1'}>}landing_hits)

[sushil353]

you can also try

sum({(<campaign_id ={' '}>) * <client_id ={1}>} landing_hits)

[berryandcherry6]

Hi all,

I am trying all answers given, but all results in zero

sum({ <client_id ={1}>} landing_hits)  results in 19343, but when i add condition to check null selection in campaign_id it shows me Zero.

I have data in database where i have checked with below query

select sum(landing_hits) from CAMPAIGNMETRICS where client_id = '1' and campaign_id is null; this results in 8900.

[jonathandienst · Accepted solution]

Set expressions work the same way as selections. Just as it is not possible to directly select a null value from the UI, it is also not possible to directly select a null value in a set expression. You may be able to do so indirectly depending on your data model.

Otherwise you have some choices:

• Use a sum(if()) expression - this may perform badly if your data set is large

Sum({<client_id ={1}>} If(Len(campaign_id) = 0, landing_hits))

• Add a flag during load for the null values and use this in the set expression

In script:

LOAD ...

    If(Len(campaign_id) = 0, 1, 0) as NullCampaign,

    ...

In expression:

Sum({(<NullCampaign={1}, client_id ={1}>} landing_hits)

• Use NULLASVALUE to convert the nulls to empty strings so they are selectable

In expression:

Sum({<campaign_id ={'null()'},client_id ={'1'}>} landing_hits)

Sum({<campaign_id ={''},client_id ={'1'}>} landing_hits)

[berryandcherry6]

Hi Jonathan,

Thanks, it worked.

I tried with all option provided, first two equation gives expected values. But third expression doesnot work.

Can you explain why it is not working? or what may be reason?

Thanks.

[Anonymous]

You Can apply following:

sum({<campaign_id ={'null()'},client_id ={'1'}>} landing_hits)

Thanks,

[jonathandienst]

>>But third expression doesnot work.

Sorry - cut and paste error - it should be

=Sum({<campaign_id ={''},client_id ={'1'}>} landing_hits)

I am amending the original post.

[berryandcherry6]

HI Jonathan,

Sum({<campaign_id ={''},client_id ={'1'}>} landing_hits)

or

sum({(<campaign_id ={' '}>) * <client_id ={1}>} landing_hits)

Though it looks logically correct, this gives me Zero.