Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
swetasingh
Partner - Contributor III
Partner - Contributor III

extract values based on variables which is user input

Hi, I have created a variable which captures phone number based on user input. 

based on user input i want to extract name of the person from phone number and use that name to extract pin code of the person from a different table. 

Table 1 : 

Ph NoName
97xxxxxxx16AB
84XXXXXX24AA
34xxxxx99AC

 

Tab 2 : 

NamePin code
AA4896
AB 4434
AC1234

 

I have defined a variable which is currently giving me the phone no, but when i use the same expression in set analysis gives no result. 

if i enter name of the person directly in the set it works but i have huge data and i want to make it dynamic. 

Below are the formulas : 

Avg({$< [Name]={'AB'}>}Pin code) - gives correct pin code

Avg({$<Name = {if( [ph no]=$v,Name}>} Pin code)

Please provide resolution. 

3 Replies
dplr-rn
Partner - Master III
Partner - Master III

try below
=Avg({$< [Ph No]={$(var)}>}[Pin code])
swetasingh
Partner - Contributor III
Partner - Contributor III
Author

Hi thanks this expression works when there are only these tables, but with more table it is screwing up other values .. is there way when I can extract name based on phone number input n save it in a variable and then use that variable to get the values ????

i tried using below it didn’t work : 

if ( [ph no] = $v, Name, 0)

dplr-rn
Partner - Master III
Partner - Master III

Not sure how it can screw up the values if the name is associated with a phone number.
i cant see how saving it into a variable is very different from what i suggested but as you know your data better.
try this as new variable
Only({$< [Ph No]={$(v)}>}Name)
note that this will give null if no phone number is inputted so your final expression using this variable should take that into account