Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

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. 

Tags (2)
3 Replies
Partner
Partner

Re: extract values based on variables which is user input

try below
=Avg({$< [Ph No]={$(var)}>}[Pin code])
Partner
Partner

Re: extract values based on variables which is user input

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)

Partner
Partner

Re: extract values based on variables which is user input

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