topic Re: Issues due to nulls in QlikView

Issues due to nulls

kishorj1982 — Mon, 20 Nov 2017 09:45:36 GMT

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

Re: Issues due to nulls

PrashantSangle — Mon, 20 Nov 2017 09:56:34 GMT

I am getting same value after clear all selection and by selecting ActiveInactiveFlag as 'All'

It is -950761.74431.

Regards,

Re: Issues due to nulls

kishorj1982 — Mon, 20 Nov 2017 09:58:28 GMT

I have uploaded the new file.

Thanks

Re: Issues due to nulls

PrashantSangle — Mon, 20 Nov 2017 10:05:56 GMT

add all values in pick(match()) against all search type

Regards,

Re: Issues due to nulls

stabben23 — Mon, 20 Nov 2017 10:09:45 GMT

If you look into ActiveInactiveFlagtemp Field, everything looks to work.

Maybe try to delete this part:

Flag:
Load ActiveInactiveFlagtemp,
ActiveInactiveFlagtemp as ActiveInactiveFlag
Resident AddressBook;
Concatenate
Load ActiveInactiveFlagtemp,
'All' as ActiveInactiveFlag
Resident AddressBook;

Re: Issues due to nulls

kishorj1982 — Mon, 20 Nov 2017 12:48:37 GMT

That part is required.

Because for All the values should be there after selection for All option in Active inactive flag.

Thanks,

Re: Issues due to nulls

kishorj1982 — Mon, 20 Nov 2017 12:58:37 GMT

RhanksWe need to show C , CP for Active selection.

Cx for inactive selection.

C,CP,CX and NULL in All selection.

In pick match function we are not able to repeat the values so I used

Thanks

Re: Issues due to nulls

sunny_talwar — Mon, 20 Nov 2017 13:17:47 GMT

What exactly are you trying to do here?

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

This is showing up as this...

Is this something you expected to see?

Re: Issues due to nulls

kishorj1982 — Mon, 20 Nov 2017 13:32:26 GMT

Whenever I am selecting All values for search type C,CP,CX ,'' also need to be displayed.

For Active-c,cp

Inactive-cx

all- C,CP,CX ,and blank

Re: Issues due to nulls

sunny_talwar — Mon, 20 Nov 2017 13:34:42 GMT

What about other Search_Type? J, U, E, B, EX, V? Those don't need to be selected?

Re: Issues due to nulls

pradosh_thakur — Mon, 20 Nov 2017 13:35:21 GMT

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

Re: Issues due to nulls

sunny_talwar — Mon, 20 Nov 2017 13:37:10 GMT

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);

Re: Issues due to nulls

kishorj1982 — Mon, 20 Nov 2017 13:56:42 GMT

we need it for all selection.

thanks

Re: Issues due to nulls

sunny_talwar — Mon, 20 Nov 2017 14:03:35 GMT

I don't think I understand?

we need it for all selection.

Re: Issues due to nulls

kishorj1982 — Tue, 21 Nov 2017 04:21:17 GMT

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.

Re: Issues due to nulls

pradosh_thakur — Tue, 21 Nov 2017 05:26:08 GMT

try this in the script

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

regards

Pradosh

Re: Issues due to nulls

sunny_talwar — Tue, 21 Nov 2017 12:08:19 GMT

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

Re: Issues due to nulls

kishorj1982 — Tue, 21 Nov 2017 12:15:54 GMT

'That should be All.

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

Re: Issues due to nulls

sunny_talwar — Tue, 21 Nov 2017 12:21:33 GMT

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

Re: Issues due to nulls

sunny_talwar — Tue, 21 Nov 2017 12:30:16 GMT

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;