Show only those rows which contain value...

[hamzabob1]

Hello all,

I have one problem can u please help me out.

my Question is:-

Item, Sales, Budget

A1, 100, --

A2, 200, --

A3, 300, --

A4, 200, --

A1, --, 90

A2, --, 150

A3, --, 300

Now I want to fetch the Items, whose Sales and Budget is presented.

The result should be: A1, A2, A3 [A4 – doesn’t have any budget].

thanks in advance.

[sunny_talwar]

Try this in a text box object:

=Concat(DISTINCT {<Item = {"=Count({<Sales = {'*?'}>}Sales) = Count({<Budget = {'*?'}>}Budget)"}>} Item, ',')

Ouput:

HTH

Best,

Sunny

[sunny_talwar]

swuehl‌ putting my new knowledge to work here .

[antoniotiman]

Hi,

if You want rows filtered in Script, try

LOAD *

Where Sales > 0 and Budget > 0;
LOAD DISTINCT
Item,Sum(Sales) as Sales,Sum(Budget) as Budget
Group By Item;
Load *
Inline [
Item, Sales, Budget
A1, 100, --
A2, 200, --
A3, 300, --
A4, 200, --
A1, --, 90
A2, --, 150
A3, --, 300
A5, --, 500
];

Regards,Antonio

[antoniotiman · Accepted solution]

If You want to use within chart

LOAD all rows

in Chart Calculated Dimension

=Aggr(If(Sum(Sales) > 0 and Sum(Budget) > 0,Item),Item)

Regards,

Antonio

[Anonymous]

Table:

load

Item,

Sales,

Budget

where Sales>0

and  Budget>0

;

load

Item,

sum(Sales) as Sales,

sum(Budget) as Budget

Group by

Item

;

LOAD * Inline [

Item, Sales, Budget

A1, 100,

A2, 200,

A3, 300,

A4, 200,

A1, , 90

A2, , 150

A3, , 300

]

[Anonymous]

Another approach i would do is, load Item and Sales first where len(Sales)> 0 and load Item and Budget where len(Budget)>0. Then join those two tables. You will get the right data in the script itself.

[maxgro]

=Concat(DISTINCT {<Item={"=sum(Sales)<>0"}*{"=sum(Budget)<>0"}>} Item, ',')

[hamzabob1]

hii sunindia,

its not working in listbox...

i want to show required output in listbox insted of textbox...

thanks in advance...

[hamzabob1]

Thanks for useful information ANTONIO MANCINI..

Thanks...