Issues due to nulls

[kishorj1982]

Hi all,

PFA.

I have nulls in my  ActiveInactiveFlag.

for handling those I use pick match function.

But still value after'All' selection are not matching in active inactive flag.

My requirement is values after clear all and values after selecting All option for sum(CollectionAmount) should match.

Please suggest.

thanks

[pradosh_thakur]

Hi

Please refer the below screenshot . WHEN i am selecting 'ALL' there are some non associated value in searchtype. If you add that value with the value you get with 'All'. It will match up.

regards

Pradosh

[sunny_talwar]

May be use this expression

=Sum({<ActiveInactiveFlagtemp = {"*"}>}CollectionAmount)

With this script

NoConcatenate

Flag:

LOAD DISTINCT ActiveInactiveFlagtemp,

ActiveInactiveFlagtemp as ActiveInactiveFlag

Resident  AddressBook

Where not IsNull(ActiveInactiveFlagtemp);

Concatenate (Flag)

LOAD DISTINCT ActiveInactiveFlagtemp,

'All' as ActiveInactiveFlag

Resident AddressBook

Where not IsNull(ActiveInactiveFlagtemp);

[kishorj1982]

we need it for all selection.

thanks

[sunny_talwar]

I don't think I understand?

we need it for all selection.

[kishorj1982]

For All selection all search type need to be considered.

Your condition in set analysis is working fine for me.

For active selection value for serach type sjould be c,CP

inaction values of search type should be CX.

My question is when ever I am doing same calcumation in scrupt it is giving me wrong results.

i would like to move this calculation in script level.

your solution working fine for me but I have some null vaules in Area , these values I need to show.

Flags are not associated with that so results are not matching.

I am not unable to perform suppress nulls for those.

Thanks.

[pradosh_thakur]

try this in the script

Pick(match(Search_Type,'C','CP','CX','')+1,'All','Active','Active','Inactive','All') as ActiveInactiveFlagtemp

regards

Pradosh

[sunny_talwar]

What are these needed to be assigned for flagtemp? All?

[kishorj1982]

'That should be All.

all values should be there in All including C,CP,CX and blank.

[sunny_talwar]

For FlagTemp or Flag? I am asking about FlagTemp right now

[sunny_talwar]

Check the attached

Script changes.... New AddressBook Script

AddressBook:

LOAD *,

     If(IsNull(ActiveInactiveFlagtemp), 'Null', ActiveInactiveFlagtemp) as FlagTemp,

LOAD Address_Number as CustNo,

     Address_Number as CustNo_address,

     Pick(match(Search_Type,'C','CP','CX',''),'Active','Active','Inactive','All') as ActiveInactiveFlagtemp,

     Search_Type,

     If(Len(Location) > 0, Location)  as Location,

     Sales_Rep as SalesRep,
     LineOfBusiness

FROM (qvd);

And then add this

NoConcatenate

Flag:

LOAD DISTINCT FlagTemp,

If(FlagTemp = 'Null', Null(), FlagTemp) as ActiveInactiveFlag

Resident  AddressBook;

Concatenate (Flag)

LOAD DISTINCT FlagTemp,

'All' as ActiveInactiveFlag

Resident AddressBook;