topic Re: Halfway elegant solution for a formula required in QlikView

Halfway elegant solution for a formula required

datanibbler — Thu, 18 Jun 2015 12:06:50 GMT

Hi guys,

I want to implement the following logic:

I want to calculate a new field:

- If one field contains the exact string 'LVP', I want to return the nr. 6

- If it contains the exact string 'LVG', I want to return the nr. 9

- If it contains the string 'LV' in some place and maybe either 'P' or 'G' in some place, but also other letters, I want to
return the nr. 0

- In all other cases (e.g., if that field doesn't contain the letters 'LV' at all) i want to return the value of another field, say [fieldA}

I could probably do that with an IF_function, but several nested IF_functions are not very elegant and not very easily manageable due to the large nr. of brackets to be closed at the very end or someplace in the middle of it.

Is there any more elegant solution to this?

I have thought about using PICK(MATCH()) or alt(), but haven't come to a satisfying solution.

Thanks a lot!

Best regards,

DataNibbler

Re: Halfway elegant solution for a formula required

Clever_Anjos — Thu, 18 Jun 2015 12:15:05 GMT

Please try this

=pick(WildMatch(Field,'LVP','LVG','LV*P','LV*G')+1,

'fieldA',

)

Re: Halfway elegant solution for a formula required

datanibbler — Thu, 18 Jun 2015 12:19:31 GMT

Hi,

that is what I was thinking about. Is it certain that this will match top-down (from left to right, thus what has been matched to 'LVP' or 'LVG' won't be matched against any of the following values)?

This only lacks some way of dealing with all the items that do not match any of these (that do not contain the letters 'LV' at all)

Could this be wrapped inside an alt() function so that whatever isn't covered by this will return another value?

Re: Halfway elegant solution for a formula required

swuehl — Thu, 18 Jun 2015 12:27:55 GMT

Clever is taking care of the non-matching case by adding a 1 to the returned value of zero (in case of no match) from the WildMatch function.

Thus, the second argument of pick() (i.e. the expression to be used in case of '1') will be the default value.

I think there is just another case '0' missing at the end of the pick() argument list.

Re: Halfway elegant solution for a formula required

datanibbler — Thu, 18 Jun 2015 12:34:39 GMT

Hi swuehl,

this is solved I think. I just realized I had to adapt one of my earlier formulas so it matches this one, but now I'm quite convinced that this part of the analysis is robust and stable and I can go on.

Thanks a lot!

Best regards,

DataNibbler

Re: Halfway elegant solution for a formula required

Clever_Anjos — Thu, 18 Jun 2015 13:35:17 GMT

If does not match, WildMatch returns 0, so 0+1 will evaluate to 1 and will be handled for the first clause.

Yes, you can wrap ALT(), but I don´t see a way it could be null