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: 
gadwinjer
Partner - Contributor III
Partner - Contributor III

How to retrieve all values which have the left(value, n) in common with the selection

Hello, it's quite hard for me to explain :
I need to retrieve all childrens and all parents of my selected value. Suppose a table like this :

IDTest1IDTest2
01#elem1
01#01#elem2
01#02#elem3
01#02#01#elem2
01#02#02#elem4


I would like to know all parents of elem3 (elem1) and all his children (elem2 and elem4). My only way to know it is the IDTest1 which one contains all the parents of an IDTest2. IDTest2 is not unique because it can be used by many IDTest2. The IDTest1 is unique.

So I would like, from a selection of the IDTest2 retreive all the IDTest2 used by the selected and all the IDTest2 which use the selected value.

I really don't know how to proceed. Even if I take the selected value, how can I retrieve all IDTest2 which have the left part of IDTest1 in common with the selected one ?

Thanks

EDIT : I tried by data load but I didnt find a way to avoid two loops (one for each IDTest2 and the second (nested in the first one) for each IDTest1 corresponding to the IDTest2 and that really took too much time....

I tried many things by measure expression but for now, I m not good enough and need your help . To simplify my objective I only had to select the IDTest1 (the final objective is to get the same result by focusing the IDTest2) and I tried this kind of things :

if(left([IDTest1], len(getfieldselections([IDTest1])) - 3)  = getfieldselections([IDTest1]), IDTest2)
I also tried many things like  count({1} if(left([IDTest1], len(getfieldselections([IDTest1])) - 3)  = getfieldselections([IDTest1]), IDTest2)) to avoid the range of my selection and try to get something but ... no result !

I hope my english is understandable

Ce message a été modifié par : Jérémy Bonde

1 Solution

Accepted Solutions
gadwinjer
Partner - Contributor III
Partner - Contributor III
Author

I've found a way to do this and it's so simple that I'm ashamed to have posted this question :

Parents:

LOAD

IDTest2,

left([IDTest1] , len([IDTest1]) - 2) as Parent

resident TableTest;

join(Parents)

LOAD

IDTest2 as ParentIDTest2,

[IDTest1] as Parent

resident TableTest;

Children:

LOAD

IDTest2,

[IDTest1] as Children

resident TableTest;

join(Children)

LOAD

IDTest2 as ChildrenIDTest2,

left([IDTest1] , len([IDTest1]) - 5) as Children

resident TableTest;

So it's easy after with these tables

View solution in original post

3 Replies
swuehl
MVP
MVP

This might get complicated especially given the fact that your IDTest2 values are not unique.

In general, I would have suggested looking into the HIERARCHY / HIERARCHYBELONGSTO Load prefixes available in the script:

Hierarchies

But as said, this might not really work in your case.

Attached a sample QVW that demonstrates how you may be able to do the value matching assuming a single value is selected.

gadwinjer
Partner - Contributor III
Partner - Contributor III
Author

I've found a way to do this and it's so simple that I'm ashamed to have posted this question :

Parents:

LOAD

IDTest2,

left([IDTest1] , len([IDTest1]) - 2) as Parent

resident TableTest;

join(Parents)

LOAD

IDTest2 as ParentIDTest2,

[IDTest1] as Parent

resident TableTest;

Children:

LOAD

IDTest2,

[IDTest1] as Children

resident TableTest;

join(Children)

LOAD

IDTest2 as ChildrenIDTest2,

left([IDTest1] , len([IDTest1]) - 5) as Children

resident TableTest;

So it's easy after with these tables

swuehl
MVP
MVP

Yeah, this looks much easier when you do it in the script.

Should also handle your multiple occurences of IDTest2 fine.

Have fun,

Stefan