topic Re: Synthetic Fields as Dimension in QlikView

Synthetic Fields as Dimension

Qvmaster2019 — Mon, 17 Apr 2017 02:25:37 GMT

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

=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) :

=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?

Re: Synthetic Fields as Dimension

Qvmaster2019 — Mon, 17 Apr 2017 05:46:00 GMT

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.

Re: Synthetic Fields as Dimension

ganeshsvm — Mon, 17 Apr 2017 06:23:39 GMT

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

Re: Synthetic Fields as Dimension

Qvmaster2019 — Mon, 17 Apr 2017 06:43:32 GMT

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.

Re: Synthetic Fields as Dimension

ganeshsvm — Mon, 17 Apr 2017 06:50:21 GMT

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')

Re: Synthetic Fields as Dimension

Qvmaster2019 — Mon, 17 Apr 2017 06:55:14 GMT

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 "

Re: Synthetic Fields as Dimension

ganeshsvm — Mon, 17 Apr 2017 07:48:32 GMT

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.

Re: Synthetic Fields as Dimension

Qvmaster2019 — Mon, 17 Apr 2017 07:55:35 GMT

Yes, this is what I have as well.

Re: Synthetic Fields as Dimension

ganeshsvm — Mon, 17 Apr 2017 07:59:47 GMT

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

Re: Synthetic Fields as Dimension

vinieme12 — Mon, 17 Apr 2017 09:10:35 GMT

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

Re: Synthetic Fields as Dimension

Qvmaster2019 — Mon, 17 Apr 2017 09:48:15 GMT

Current requirements: The output required is a pivot table with :

Dim1:Top N Countries based on Sales on selected Month.

Dim2: Year 1 and Year 2 (based on user selection)

DIm 3: Periods in the form of 4 Quarters Ago, 3 Quarters Ago, 2 Quarters Ago, 1 Quarter Ago, YTD, MTD, Previous Month, 2months ago appearing at the same time where Year depends on Dim2

Dim 4: Bucketing of Sales: e.g. All Range, 0-$300, 300-600, 0-600. Range can be dynamic based on user input.

Dim 5: Materials in this format:

Material 1

Material 2

Material 3

Material 4

Material 5

Material 6

Material 7

Where Material 1 is the latest Material for that country

Material 2 is the top selling Material for that country except Material 1

Material 3 is the top selling Material for that country except Material 1 and Material 2

Material 4 is the TOTAL of all Materials flagged as "Special"

Material 5 is the top Material flagged as "Special" but not Material 1, Material 3 and Material 4

Material 6 is the 2 top Material flagged as "Special" but not Material 1, Material 3 and Material 4 and Material 5

Material 7 is the top 1 Material NOT flagged as "Special" but not Material 1, Material 3 and Material 4

Everything is working with valuelist() then comes Dim6 as the new requirement

Dim6: Categorized Dim3 if "Quarterly", "Yearly" or "Monthly"

Re: Synthetic Fields as Dimension

Qvmaster2019 — Mon, 17 Apr 2017 09:49:44 GMT

I'm looking for one that will not touch the expression anymore. Or any alternative solution that I can play with.

Re: Synthetic Fields as Dimension

ganeshsvm — Mon, 17 Apr 2017 10:53:58 GMT

Can you post some sample data in Excel?

Re: Synthetic Fields as Dimension

vinieme12 — Mon, 17 Apr 2017 10:59:13 GMT

can you post sample app/ sample data?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Synthetic Fields as Dimension

Qvmaster2019 — Tue, 18 Apr 2017 01:45:25 GMT

Data is nothing fancy. Very straight forward.

Country	Model	Type	Period	Sales
US	AAA	Special	04 18 2017	$ 98,389
US	BBB	Regular	04 18 2017	$ 11,229
US	AAA	Special	04 18 2016	$ 29,084
US	BBB	Regular	04 18 2016	$ 37,980
AU	AAA	Special	04 18 2017	$ 79,867
AU	CCC	Regular	04 18 2017	$ 15,469
AU	AAA	Special	04 18 2016	$ 11,328
AU	DDD	Regular	04 18 2016	$ 61,720

Re: Synthetic Fields as Dimension

Qvmaster2019 — Tue, 18 Apr 2017 01:45:48 GMT

Data is nothing fancy. Very straight forward.

Country	Model	Type	Period	Sales
US	AAA	Special	04 18 2017	$ 98,389
US	BBB	Regular	04 18 2017	$ 11,229
US	AAA	Special	04 18 2016	$ 29,084
US	BBB	Regular	04 18 2016	$ 37,980
AU	AAA	Special	04 18 2017	$ 79,867
AU	CCC	Regular	04 18 2017	$ 15,469
AU	AAA	Special	04 18 2016	$ 11,328
AU	DDD	Regular	04 18 2016	$ 61,720