prefilter a dimension (and only allow the prefiltered selection)

[olivier_le_roux]

hi, i need to use a few selection of a dimension Value, and fix it. example: i have a dimension "vendors" and i want to select an fix only 5 of them on a 30 list: vendors: A, B, C, D, E, F,......, Z, AA, AB I only want A, F, R, Y, AB I want to add this new dimension in a rapport. I don't want to use a filter after creation. Is it Possible? sorry for my wonderful Frenglish or englench.... best regards!

[sicdude]

try this
={$< [vendors]={A,F,R,Y,AB} >} [vendors]

[olivier_le_roux]

hi,

thanks for the answer, it doesn't works.

any suggestion?

[ArnadoSandoval]

Hi Oliver

Can you try removing the [Code Vendeur] at the end of your expression!

Hope this helps,

Arnaldo Sandoval

[olivier_le_roux]

 Hi Arnaldo,

it doesn't works.

i still have the alert "garbage after the expression $"

i tried to create a new dimension, with the original table, but i don't have possibility to add the limitation.

perhaps because I don't use the right action?

[ArnadoSandoval]

Hi Oliver,

Are you counting Vendors? perhaps you should include a COUNT() or SUM() function, like this

=Count( {$<[Code Vendeur]={ ... } > } [Code Vendour] )

The code just count the [Code Vendour] for just the one on the list,  (I guess it should return 😎 but you probable want to SUM one of their measures, like sales, in that case, replace the second [Code Vendour] with the column you want to SUM.

Hope this helps,

Arnaldo Sandoval

[olivier_le_roux]

no, 

I don't want to count, I just want conserve a few list of vendors, and create a new dimension with them. I try to create this dimension with an existant table, named [vendors]

I tried your proposition but it doesn't work (gave "unvalid dimension")

the existant dimension is 

=[vendors] 

[vendors] has several items 

the new one is on the same table [vendors], but with a limited list of them. (take 10 of X thousand items)

I'm Sorry for my wonderful Frenglish! 😉

[ArnadoSandoval]

Hi Oliver,

I am working on a solution to your problem, I will get back to you in about two hours, as I have something to do in that time! actually I got it working but I want to polish it a bit.

Regards,

Arnaldo Sandoval

[olivier_le_roux]

thank you Arnaldo, take your time, no worry! 

[ArnadoSandoval · Accepted solution]

Hi Oliver,

I played around with your issue by mocking up a Vendors table and implementing a Selections table;  my Vendors table has only four columns: Code, Name, Group and Value; I added 10 dummy vendors with silly names, two groups (G1, G2) and funny values 🙂; I also created a variable vVendorList mimicking the list of vendors with special treatment. This is my script, out of this script perhaps the Selections table and the vVendorList variable as important to you, so keep an eye on them.

NoConcatenate
Vendors:
Load * Inline [
Code, Name, Group, Value
111,A,G1,123
112,B,G2,234
113,C,G1,555
114,D,G1,333
115,E,G2,222
116,F,G1,1123
117,G,G2,1234
118,H,G1,1555
119,I,G1,1333
120,J,G2,1222
];

NoConcatenate
Selections:
Load * Inline [
OptValue
All
Special
]; 

Set vVendorList=['111','115','118'];

The vendor list for this tests are 111, 115 and 118;  The Selections table has two options: All and Special; The test user interface is shown below:

It has 5 panels:

1. Options
2. Selected Option
3. Straight Table
4. Table
5. Graphics

The screenshot shows the UI when the Special selection is taken, e.g. the vendor list;  The Panel 3 with the Straight Table will not work for us, as you can see the Table on Panel 3 shows the vendors in the Vendors List variable, that is what you want to achieve. None of the vendors on the Vendors List belong to the group G2, so it is not showing; Panel 2 shows our current selection and Panel 1 is the selector.

Let's comment on the expressions behind each panel:

Panel 1: It is just the selector on the column =OptValue.

Panel 2: It is actually our current selection, its expression is:  =GetFieldSelections(OptValue)

Panel 3: It is the Straight table, it will never show us the results we want, anyhow, it has two expressions; The Code column expression is:

=if(GetFieldSelections(OptValue)='All', Code, if(match(Code,$(vVendorList))=0, Null, Code))

While the Sales column expression is:

=if(GetFieldSelections(OptValue)='All', Sum(Value), Sum( {$<Code={$(vVendorList)}>} Value))

I will explain these expressions later.

Panel 4: The Table, it also has two expressions,  exactly the same expression implemented into the Straight Table (Panel 3); Here, on the Code column we un-selected the option Include null values.

Panel 5: The Graphic shows Sales per Group for All or the Vendor List; We implemented this expression on the bar column:

=if(OptValue='All', Group, 'G1')

I am cheating here, because the Group selection is not driven by the Vendor List, this is a false positive.

The formulas:

• We are using the function GetFieldSelections(OptValue) to tell apart when to use all the codes or just the list of the vendors.
• We are also using this function match(Code,$(vVendorList)) it returns 0 (zero) when the Code is not in the Vendors List, a positive value otherwise.
• We are also using this function Sum( {$<Code={$(vVendorList)}>} Value) at the Panel 4's Table on the sales column. It may not be required, I think we could use Sum(Value) with no dramas because the Code column is already suppressing nulls.

Please review my reply, writing it help me to sharp my Qlik skills, so I hope it helps you to manage your data as required.

Best regards,

Arnaldo Sandoval