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: 
Qvmaster2019
Creator
Creator

Synthetic Fields as Dimension

Using the same synthetic fields as dimensions, I have this.  It is like "1:Many".

#1

                             

=ValueList('John','Mary','Joseph')

=ValueList('John','Mary','Joseph')

1

John

John

1

John

Mary

1

John

Joseph

1

Mary

John

1

Mary

Mary

1

Mary

Joseph

1

Joseph

John

1

Joseph

Mary

1

Joseph

Joseph

1

But what I want is this (more like 1:1) :

#2

           

=ValueList('John','Mary','Joseph')

=ValueList('John','Mary','Joseph')

1

John

John

1

Mary

Mary

1

Joseph

Joseph

1

I can achieve #2 if I move one of synthetic fields to become expression but I need both to be in dimension.

Another restriction,  values inside the valuelist function will become dynamic later on depending on various selection. This means I cannot do this in load script.

Anyway to achieve this?

15 Replies
Qvmaster2019
Creator
Creator
Author

This is what I have so far, I edit one of the valuelist to be a little bit different. Then modified the expression like this.

=if(
ValueList('Mary','Joseph','John')='Mary' AND if(ValueList('John','Mary','Joseph')='Mary',1,0),
1,
if(
ValueList('Mary','Joseph','John')='Joseph' AND if(ValueList('John','Mary','Joseph')='Joseph',1,0),
2,
if(
ValueList('Mary','Joseph','John')='John' AND if(ValueList('John','Mary','Joseph')='John',1,0),
3,
)
)
)

If there is any other way, kindly post here.

ganeshsvm
Creator II
Creator II

Hi Darwin,

Since you are using the same values in both Synthetic Dimension, the chart is not able to identify from your expression which dimension you are making conditional and you have used ValueList('Mary','Joseph','John')='Mary' in your expression which doesn't match with the synthetic dimension(order is not the same), so it wont be able to identify the exact dimension.

You can use Synthetic dimension like this in your case to get your expected result,

Dim1 = Valuelist('Mary, 'Joseph', 'John') and Dim2 = Valuelist('John', 'Mary, 'Joseph')

and use the same expression which you have used to get the expected result.

Regarding dynamic values, you need to elaborate more on your requirement.

Hope this helps.

-Ganesh

Qvmaster2019
Creator
Creator
Author

For dynamic values, this is  of no concern so far. I will just have the constants inside the valuelist become variables.

For the changing of dimensions definition, so far, I have the same approach but if there is other, i will be glad to check it out.

ganeshsvm
Creator II
Creator II

Since you quoted this,

For the changing of dimensions definition, so far, I have the same approach but if there is other, i will be glad to check it out.

Just to confirm, have you already tried the below solution?

Dim1 = Valuelist('Mary, 'Joseph', 'John') and Dim2 = Valuelist('John', 'Mary, 'Joseph')

Qvmaster2019
Creator
Creator
Author

darwin.natividad wrote:

This is what I have so far, I edit one of the valuelist to be a little bit different. Then modified the expression like this.

=if(
ValueList('Mary','Joseph','John')='Mary' AND if(ValueList('John','Mary','Joseph')='Mary',1,0),
1,
if(
ValueList('Mary','Joseph','John')='Joseph' AND if(ValueList('John','Mary','Joseph')='Joseph',1,0),
2,
if(
ValueList('Mary','Joseph','John')='John' AND if(ValueList('John','Mary','Joseph')='John',1,0),
3,
)
)
)

If there is any other way, kindly post here.

Yes, I did. Notice my expression, it was made possible because of minor changes in the dimension definition.

Changing the dimensions alone will not cut it out; it will still display "1:Many "

ganeshsvm
Creator II
Creator II

BTW I could achieve the solution which you wanted,

I used this Dimensions:

Dim1: ValueList('John', 'Mary', 'Joseph') and Dim2: ValueList('Mary', 'Joseph', 'John')

& used this Expression:

=if(
ValueList('Mary','Joseph','John')='John' AND ValueList('John','Mary','Joseph')='John',1,

IF(ValueList('Mary','Joseph','John')='Mary' AND ValueList('John','Mary','Joseph')='Mary',1,

if(ValueList('Mary','Joseph','John')='Joseph' AND ValueList('John','Mary','Joseph')='Joseph',1,
)
)

and chart properties, Suppress Zero values & Missing values.

thread1251425_soln.JPG

Qvmaster2019
Creator
Creator
Author

Yes, this is what I have as well.

ganeshsvm
Creator II
Creator II

Then I guess you have your solution or what you else do you expect?

vinieme12
Champion III
Champion III

Why are you using valuelist() ? why not load an inline table and have it associated with your FACT to have a Dynamic solution?

Valuelist() will be very tedious to work with

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.