topic Re: Show only those rows which contain value... in QlikView

Show only those rows which contain value...

hamzabob1 — Fri, 10 Jul 2015 05:49:40 GMT

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.

Re: Show only those rows which contain value...

sunny_talwar — Fri, 10 Jul 2015 14:07:59 GMT

Try this in a text box object:

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

Ouput:

HTH

Best,

Sunny

Re: Show only those rows which contain value...

sunny_talwar — Fri, 10 Jul 2015 14:08:42 GMT

swuehl‌ putting my new knowledge to work here .

Re: Show only those rows which contain value...

antoniotiman — Fri, 10 Jul 2015 14:23:45 GMT

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

Re: Show only those rows which contain value...

antoniotiman — Fri, 10 Jul 2015 14:37:33 GMT

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

Re: Show only those rows which contain value...

Anonymous — Fri, 10 Jul 2015 15:35:01 GMT

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

]

Re: Show only those rows which contain value...

Anonymous — Fri, 10 Jul 2015 15:53:45 GMT

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.

Re: Show only those rows which contain value...

maxgro — Fri, 10 Jul 2015 20:14:44 GMT

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

Re: Show only those rows which contain value...

hamzabob1 — Sat, 11 Jul 2015 06:45:10 GMT

hii sunindia,

its not working in listbox...

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

thanks in advance...

Re: Show only those rows which contain value...

hamzabob1 — Sat, 11 Jul 2015 07:07:58 GMT

Thanks for useful information ANTONIO MANCINI..

Thanks...