PICK(MATCH()) with n:1 matching?

[datanibbler]

Hi,

I wonder: Yesterday I was working with a colleague on an 8_line_formula made up of I_don't_know_how_many nested IF_clauses.

That makes for a tremendously complicated formula, very error-prone, and a large number of brackets - so it's very easy to misplace one and the QlikView_script_editor does not keep up and just matches the nr. of opening and closing brackets.

In other instances, I have successfully replaced nested IFs with the above_mentioned PICK(MATCH()) function, that is so much more orderly for you have exactly one opening and one closing bracket per line, not ten closing brackets at the very end and such ...

The difference is, in those nested IF-constructs there are n:1 matches - several values are matched to one.

=> Can I do that in a PICK(MATCH()) construction?

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

you are absolutely right if you try to simplify these expression. I think pick(match()) could handle n:1 matches if really each possible n is defined. Maybe a wildmatch() could give here more flexibility.

Beside the functions could variables (maybe with parameter) make the expression easier.

- Marcus

[datanibbler]

Hi Marcus,

ah, yes, I can think of a way I could do that:

=> I could instead use a PICK(Wildmatch()) function.

=> If I understand correctly the structure of that function, I could just have several instances of the same "output_value" (for the PICK()) for several different values (inside the WildMatch())

Would that work - or is there an easier/ more elegant solution?

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

if your values are relative homgogenous and you could avoid false matching it would work, if not you need a more detailed matching.

Maybe an alternatively could be to transfer it into the script with a mapping-table.

- Marcus

[datanibbler]

Hi Marcus,

I have now constructed, albeit for a new purpose (to the effect of the sheets in an app dynamically "flipping through" like a presentation) a rather longish formula using the PICK(MATCH()) approach.

Since it is rather long, I attach it.

The thing is:

- In that app, there are 15 sheets

- I divide an hour into quarters, so every sheet should be activated once every 15min.

=> I have four values (of > Minute(Now()) <) each triggering the same sheet

=> For reasons of simplicity, I write those right one after another and then I have four consecutive lines with the same
     sheet_ID.

As the formula is rather long (and I have only a quarter yet, I need to include 60 values), I attach it.

It seems that the sheets are just not being activated. Can you tell me why it would not work?

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Ah - could it be that, as I have set that trigger on > upon change of value < (I only have two options with variables), the variable would have to change every minute to trigger the activation of a new sheet?

[marcus_sommer]

Maybe you could make the expression smaller/easier with a further nested pick(match()) over the hours - your minute-pick could be reduced then on 1, 16, 31, 46.

I'm not sure if you could create a stable trigger with now(). I have struggled with them many times and have it never used productively. To use now() as trigger you need to set further parameter in it, like now(1).

- Marcus

[datanibbler]

Hi Marcus,

you're right, Now() is tricky. I use it without parameter - the default is 1, so it should be evaluated continuously and thus the sheet should flip every minute - but it does not. I have a variable for the minute that does change every minute, but the sheets don't.

Can you propose an alternative?

[marcus_sommer]

Hi DataNibbler,

I believe the reason is that the trigger is a variablen-change through an expression. Unfortunately didn't this work, for me it's a bug and none feature. Maybe you could set these expression directly in the action-expression or used macros. An alternatively could be as well extensions or direct discovery.

- Marcus

[datanibbler]

Hi Marcus,

how could I set the expression directly in the action? I'm not sure I understand.

Using an additional macro would be resource_heavy and direct_discovery would make things overly complicated.

I might as well try to set the browser to display a different URL every minute and just concatenate a different sheet_ID to the URL every minute.

I will ask our external QlikView_consultant how he did it - he told me he has implemented sth like this at another company.

We'll see ... that's what the blind man said 😉