topic Re: nested IntervalMatch? Is it even possible? in QlikView

nested IntervalMatch? Is it even possible?

Thu, 18 Jul 2013 08:52:15 GMT

Hi, Basically I have 3 ranges of organisation structure levels for each user:

UserJoe

Org1 = From AAAA To CCCC

Org2 = From FFFF To KKKK

Org3 = From XXXX To ZZZZ

UserMary

Org1 = From BBBB To CCCC

Org2 = From LLLL To OOOO

Org3 = From XXXX To ZZZZ

Now each record has:

Record Org1 Org2 Org3

1000 B F Y

1001 C G Z

1002 A K X

I need your help in finding a way to link the Users to the Records where only they have the permission, ie, the record falls in their organisation structure.

Any ideas please? I thought of the IntervalMatch and then realised I might need to nest the 3 levels together but I dont know the syntax for it and cannot find it online 😕

Thanks

Melody

Re: nested IntervalMatch? Is it even possible?

hic — Thu, 18 Jul 2013 13:12:33 GMT

If you interpret the ranges using radix 36 (see http://en.wikipedia.org/wiki/Base_36), you can use intervalmatch. However, you need several intervalmatches joined. The attached script should do the trick.

One complication is how to interpret a single letter. I have in my solution made all "numbers" 4 characters long, i.e. I have appended several zeros after the single letter, thus converting 'F' to 'F000'.

HIC

Re: nested IntervalMatch? Is it even possible?

Fri, 19 Jul 2013 03:53:09 GMT

Thanks a million, Henric!!

That really gave us a massive leg up

However we still have one unresolved issue I did not outline correctly before ... A record should only be correct if the org levels IN ORDER OF 1 - 2 - 3 are satisfied.

So if both 1001 and 1002 satisfy level 1, but only 1001 satisfies level 2, then only 1001 should show up in my data set for that user 😕

Therefore I need to create something on the lines of this algorithm:

if Org1 IntervalMatch returns True then

if Org2 IntervalMatch returns True then

if Org3 IntervalMatch returns True then

DISPLAY CLAIM

I realise that what we are trying to achieve is complicated and we really appreciate your help

Thanks

Melody

PS. I attached the Sample Databse we are trying to use

Re: nested IntervalMatch? Is it even possible?

hic — Fri, 19 Jul 2013 12:01:16 GMT

Not sure this is the most optimal solution, but I think it works.

HIC

Re: nested IntervalMatch? Is it even possible?

Mon, 22 Jul 2013 00:44:04 GMT

Thanks so much Henric ... you have been wonderful and helped us so much!

This works fine for us and shall be adopting it

Thanks again!

Melody

Re: nested IntervalMatch? Is it even possible?

Mon, 22 Jul 2013 07:38:28 GMT

On the side, I am also trying to now join the THIRD table (PolicyGroup), but somehow as soon as I add the table everything goes haywire.

I have now spent the last 7 hours trying to find a way to join the UserName and Policy_Group in this table successfully without disturbing the already perfect logic there, but with no success.

The table is in the Sample Db I posted earlier

I would really appreciate your help!

Thanks

Melody