topic Re: Pick Wildmach null value in QlikView

Pick Wildmach null value

realpixel — Fri, 22 Nov 2013 10:12:51 GMT

Hello,

I use the following expression

Pick(WildMatch([HMUSER2],'0','1','2','6'),'VIDES','ZU','ZP','ZL') as ZONE1 but I have some field that are blank.

I want to remplace blank value by 'VIDES', I try to use the value '0' or 'NULL' without succes.

Sommeon have an idea?

Re: Pick Wildmach null value

Anonymous — Fri, 22 Nov 2013 10:17:13 GMT

I like sorting this in the load script using the Nullasvalue() in the load script :

SET NullValue = '<Null>';

NullAsValue [YourField] ;

Best Regards, Bill

Re: Pick Wildmach null value

Jason_Michaelides — Fri, 22 Nov 2013 10:17:22 GMT

There's probably a better way but try:

If(IsNull(HMUSER2),'VIDES',

Pick(WildMatch([HMUSER2],'0','1','2','6'),'VIDES','ZU','ZP','ZL')) as ZONE1

Hope this helps,

Jason

Re: Pick Wildmach null value

Fri, 22 Nov 2013 10:32:54 GMT

Hi,

use the isnull() in the if() function

I like also the alt(arg1, arg2) function that returns the second argument when the 1st one is null

Fabrice

Re: Pick Wildmach null value

arbernardez_old — Fri, 22 Nov 2013 10:41:03 GMT

Hello,

sometimes empty value is not null, but you can easily find these blank fields with len(trim(x))=0

this should works, just take blank values and convert them to '0' to reach VIDES at pick function

Pick(WildMatch(if(len(trim([HMUSER2]))=0,'0',[HMUSER2]),'0','1','2','6'),'VIDES','ZU','ZP','ZL')) as ZONE1

Regards

Re: Pick Wildmach null value

realpixel — Fri, 22 Nov 2013 10:59:20 GMT

Thank you for your reply.

I try this expression, without succes

SET NullValue = '<Null>';

NullAsValue [YourField] ;

And try also without succes

If(IsNull(HMUSER2),'VIDES',

Pick(WildMatch([HMUSER2],'0','1','2','6'),'VIDES','ZU','ZP','ZL')) as ZONE1

Is not possible to have something like that?

pick(WildMatch(If(IsNull([HMUSER2],'VIDES','1','2','6'),'VIDES','ZU','ZP','ZL') as ZONE1,

Re: Pick Wildmach null value

Anonymous — Fri, 22 Nov 2013 11:02:25 GMT

Could elaborate on "without success" ?

Posting a sample qvw using an Inline Load for your sample would help.

Best Regards, Bill

Re: Pick Wildmach null value

arbernardez_old — Fri, 22 Nov 2013 11:02:38 GMT

Hi,

can you try this one?

Pick(WildMatch(if(len(trim([HMUSER2]))=0,'0',[HMUSER2]),'0','1','2','6'),'VIDES','ZU','ZP','ZL') as ZONE1

or also this other one,

Pick(WildMatch([HMUSER2],'0','1','2','6')+1,'VIDES','VIDES','ZU','ZP','ZL') as ZONE1

also, if you are using this '0' value thinking in null, you just need to code

Pick(WildMatch([HMUSER2],'1','2','6')+1,'VIDES','ZU','ZP','ZL') as ZONE1

because wildmatch will return 0 when no value is found, then adding 1, will situate 0 into 1 and in pick function will be VIDES

Regards

Alberto Rodríguez

Re: Pick Wildmach null value

realpixel — Fri, 22 Nov 2013 11:21:30 GMT

I try but I have an error during the reload.

Pick(WildMatch(if(len(trim([HMUSER2]))=0,'0',[HMUSER2]),'0','1','2','6'),'VIDES','ZU','ZP','ZL')) as ZONE1

or also this other one,

Pick(WildMatch([HMUSER2],'0','1','2','6')+1,'VIDES','VIDES','ZU','ZP','ZL')) as ZONE1

I try also with inline command

It's works for value 1,2 and 6 but not for Null value

HMUSER2, ZONE
1, ZU
2, ZP
6, ZL
<Null>,VIDES

];

Re: Pick Wildmach null value

Anonymous — Fri, 22 Nov 2013 11:23:54 GMT

Could you share what the error during the reload is ?

And past in your load script ?

Best Regards, Bill

Re: Pick Wildmach null value

arbernardez_old — Fri, 22 Nov 2013 11:29:45 GMT

hi,

if you have something like this:

LOAD * INLINE [

HMUSER2, ZONE

1, ZU

2, ZP

6, ZL

<Null>,VIDES

"",VIDES

*,VIDES

,VIDES

NULL,VIDES

];

and then you apply the pick like this

Pick(WildMatch([HMUSER2],'1','2','6')+1,'VIDES','ZU','ZP','ZL') as ZONE1

result will be

HMUSER2	ZONE	ZONE1
*	VIDES	VIDES
	VIDES	VIDES
<Null>	VIDES	VIDES
NULL	VIDES	VIDES
1	ZU	ZU
2	ZP	ZP
6	ZL	ZL

regards

Re: Pick Wildmach null value

realpixel — Fri, 22 Nov 2013 11:44:49 GMT

Thank you Alberto,

Sorry I had not seen have your latest solution in your previous post

The solution under works fine

Pick(WildMatch([HMUSER2],'1','2','6')+1,'VIDES','ZU','ZP','ZL') as ZONE,