topic Re: Re: How to create select distinct from list box selected value.... in QlikView

How to create select distinct from list box selected value....

Mon, 29 Sep 2014 13:53:24 GMT

I want to not need to select all other values and just select onr and get all except that registries and also sum the values for some fiels where multiple same value exist one showing total for that sum value .

I select 1 and get all records for 2.3.4 and if i have for 2 lets say 3 entries with value 1,2,3 to show 2 with column value 6

Re: How to create select distinct from list box selected value....

anbu1984 — Mon, 29 Sep 2014 14:08:49 GMT

Do you want sum all the entries except selected values?

Re: How to create select distinct from list box selected value....

Mon, 29 Sep 2014 17:44:48 GMT

No anlu ,first i need to get all values different from column except the one i will select ,after those values have other column with values that some i will need to sum ,some divide depending but only for the case where i have records more then one.

Like: If i select 1 in the table box i will have : for A column from list box i will have in table box:2,3,4 with let say sum that will be 52 and for 5 let say divide .... to ... (main problem is to make the the selection to show other then other stuff i can do )

A B

1 10

2 20

3 30

4 40

4 12

5 ...

Re: How to create select distinct from list box selected value....

JonnyPoole — Mon, 29 Sep 2014 17:50:35 GMT

Under Document Propertes -> Triggers , add a 'on select' trigger for the field in question and add an action 'select excluded' . Enter the same field name for the action.

Attached is a short example

Re: How to create select distinct from list box selected value....

Mon, 29 Sep 2014 17:58:14 GMT

Can you offer a writen example for the trigger and select excluded is an mark /unmarked section in proprieties that i read in a post but didn t do anything when i did it but will try doing all you said,just i don t have full version so can really see more then script edit code so an example writen expression needed would help a lot.

Re: How to create select distinct from list box selected value....

JonnyPoole — Mon, 29 Sep 2014 18:01:57 GMT

Does this help ?

The list box shows the field 'region'. Then i added a trigger for 'onselect' region with the action 'select excluded' on the field region.

Re: How to create select distinct from list box selected value....

JonnyPoole — Mon, 29 Sep 2014 18:55:35 GMT

Use SET ANALYIS to tweak the user's selection in the context of an individual chart or table in the UI.

For example, if a user selects the Region 'North America' , the following expression in a chart will make the chart only show Regions that DO NOT equal 'North America'.

sum( {$<Region -={'$(=Only(Region))'}>} Sales)

The '-=' is a way of saying 'NOT' . If the user makes multiple selections, the syntax would have to be improved to accommodate that scenario as the above is only for 1 selection at a time.

Re: Re: How to create select distinct from list box selected value....

Mon, 29 Sep 2014 19:08:10 GMT

I need only for a selection so it should be ok in that regard having trouble thinking how to sum up into one line the where i have copies like i said in first post for 2 linies for 4 to show only one .

A B C D to make A C D

1 261 x 10 2 y 20 for sum of then example (what i need out)

2 531 y 20 3 z 80

3 101 z 30

3 531 z 50

load * from ...

Where A not 1

and where i have

The example with region and sales as i understand will sum up all not only for where i have same column like in my exampel 3 but all sales =100 and that not what i want.

Re: Re: How to create select distinct from list box selected value....

JonnyPoole — Mon, 29 Sep 2014 19:37:36 GMT

If you sum(D) then you will get 30+50=80.

Take a look at below:

With 'Africa' selected, it shows the other Regions . But with 2007 and 2008 selected, it shows 2007 and 2008 years for those other Regions

All i used was Region and Year as dimensions with an expression of:

sum( {$<Region =- {'$(=Only(Region))'}>} Sales)

Re: Re: Re: How to create select distinct from list box selected value....

JonnyPoole — Tue, 30 Sep 2014 12:47:53 GMT

I loaded the AUFM data with the load script below. Then I created a PIVOT CHART that looks like the following. Two points:

1. 'Material' column is a constant and is just the value of the Material that the user selects. It is stored as a variable: Material = only(Material)

2. The expression causes the filter that you want. Specifically the SET statement "{$<Material =- {'$(vMaterial)'}>}" will cause only OTHER Material to show (opposite of user selection). Here is the full expression

sum( {$<Material =- {'$(vMaterial)'}>} Cantitate)

3. to add more expressions just repeat the SET statement in each expression

LOAD Doc.mat.,

[Dată doc.],

DatăÎnreg,

TMş,

Material,

LDep,

Lot,

[L/C],

[Sumă ML],

Mdă,

Cantitate,

UnB,

Cantitate1,

UnI,

ILT,

Comandă,

Rezervare,

Poz,

Mşc,

[Cont CM],

[PUoM Qty],

PUn,

QtyPUnEUn,

[PUn EUn]

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Re: Re: Re: How to create select distinct from list box selected value....

Tue, 30 Sep 2014 12:58:37 GMT

I am reading what you posted now,also see what i made that doesn wha ti need but i need to get rid of the extra value shown as they are the same in the first 3 .

Not sure i did ok but at least it show what i need...

I see you got to the same form like me but have same issue like me that it show data and material same entries,i woudl liek to see white line and just the first lien to have values 1050059 55441 06/05/2014 1000403

Re: Re: Re: Re: How to create select distinct from list box selected value....

JonnyPoole — Tue, 30 Sep 2014 13:08:11 GMT

What is the criteria that defines which row to hide ? I noticed the Lot was '-' so i enabled 'suppress NULL value' as below.

But I'm not sure that is the right criteria

Re: Re: Re: Re: How to create select distinct from list box selected value....

Tue, 30 Sep 2014 13:30:52 GMT

You don t need to supress them as if you do i m thinking the cantitate field that will be sum will not take the value you supress.

I need to supress like first field has same value for every row 10500059 i need to have one value like in transare excel exactly like that.

More simple i need to get from aufm to transare .

Like i load aufm and by script of expression in table box or etc to have a result like transare excel

1050059	55441	6/5/2014	1000403	55441	2000050	196.66	13.61	2676.543	15720.22	38.27	-15681.95
					2000131	3.9	56.19	219.141	311.75	1.35	-310.4
					2000199	61.03	17.03	1039.341	4878.5	11.75	-4866.75
					9200003

Re: Re: Re: Re: How to create select distinct from list box selected value....

JonnyPoole — Tue, 30 Sep 2014 13:32:35 GMT

OK ! I would suggest using a PIVOT TABLE chart and on the presentation tab select 'always expanded' . Then the row values won't be repeated

Re: Re: Re: Re: How to create select distinct from list box selected value....

Tue, 30 Sep 2014 13:39:24 GMT

Ya but doesn t seem to work if i do it my way .

Will try your way maybe it work in pivot table ,for me not clear how to set up pivot table as if i do dimensions the column used for the other i did t it goes bad showing nothing then a material1 field name and no data.

Re: Re: Re: Re: How to create select distinct from list box selected value....

JonnyPoole — Tue, 30 Sep 2014 14:00:42 GMT

in a straight table, expand the + on the dimension tab next to 'Command' . In the text color enter:

=if ( RowNo(total) > 1, argb(0,125,125,125))

This will make the text color transparent when the row > 1. Repeat for the dimension that you like to hide.