Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

nested IntervalMatch? Is it even possible?

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

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

HIC

View solution in original post

5 Replies
hic
Former Employee
Former Employee

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

Not applicable
Author

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

hic
Former Employee
Former Employee

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

HIC

Not applicable
Author

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

Not applicable
Author

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