Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

0,1 should be selectable through list box

Hi ,

In pivot table for no data i need to show 0 instead of '-' symbol and also in list box need to show 0,1.

when i select 0 in list box need to show 0 vales in pivot table and for 1 selection also need to show 1 values in pivot table.

Thanks,

Yuvrajesh

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Data:

LOAD

  *,

  Name & monthname as Key

Inline

[

  Name,monthname,sales

  Rajesh,Jan,100

  Rajesh,Feb,100

  Rajesh,March,100

  Rajesh,April,100

  Rajesh,May,100

  Rajesh,June,100

  Rajesh,July,100

  Rajesh,Aug,100

  Rajesh,Sept,100

  Rajesh,Oct,100

  Rajesh,Nov,100

  Rajesh,Dec,100

  Pavan,Jan,200

  Pavan,March,300

  Pavan,Oct,400

  Venu,Nov,300

  Venu,Sept,200

];

Temp:

Load Distinct Name Resident Data;

Join

Load Distinct monthname Resident Data;

Concatenate(Data)

Load *, 0 as sales Where Not Exists(Key);

Load Name, monthname, Name & monthname as Key Resident Temp;

Left Join (Data)

Load Name, monthname, If(SUM(sales)>0,1,0) as Flag Resident Data Group By Name, monthname;

Drop Table Temp;

Drop Field Key;

Now Create a table

Dimension

Name

monthname

Expression

SUM(sales)

Go to presentation tab

Untick suppress zero values.

View solution in original post

21 Replies
adamdavi3s
Master
Master

Hi,

I would replace null values with 0 in your load script, much easier all around

Generating Missing Data In QlikView

Anonymous
Not applicable
Author

i tried it not worked,could you please check attached sample qv file and please revert it back with attached ,it helps me alot.

Best regards,

Rajesh

khalander
Creator II
Creator II

Hi,

Create one inline table like below

LOAD * INLINE [

    Selection filter

    1

    0

];

write a expression in your pivot table like below

=if([Selection filter]=0,

if(isnull(sum([Val 2])),0,sum([Val 2])),

if(isnull(sum([Val 2])),0,sum([Val 2])))

replace val 2 with your metric field.

khalander
Creator II
Creator II

=if([Selection filter]=0,

if(isnull(sum([Val 2])),0,sum([Val 2])),

if(isnull(sum([Val 2])),1,sum([Val 2])))

sorry use the above expression

adamdavi3s
Master
Master

If you look at the example this is not possible as it is not a field which is null, but 'missing data'

adamdavi3s
Master
Master

Yes if you share the sample you tried it on then I would be more than happy to correct where it isn't working

MK_QSL
MVP
MVP

Data:

LOAD

  *,

  Name & monthname as Key

Inline

[

  Name,monthname,sales

  Rajesh,Jan,100

  Rajesh,Feb,100

  Rajesh,March,100

  Rajesh,April,100

  Rajesh,May,100

  Rajesh,June,100

  Rajesh,July,100

  Rajesh,Aug,100

  Rajesh,Sept,100

  Rajesh,Oct,100

  Rajesh,Nov,100

  Rajesh,Dec,100

  Pavan,Jan,200

  Pavan,March,300

  Pavan,Oct,400

  Venu,Nov,300

  Venu,Sept,200

];

Temp:

Load Distinct Name Resident Data;

Join

Load Distinct monthname Resident Data;

Concatenate(Data)

Load *, 0 as sales Where Not Exists(Key);

Load Name, monthname, Name & monthname as Key Resident Temp;

Left Join (Data)

Load Name, monthname, If(SUM(sales)>0,1,0) as Flag Resident Data Group By Name, monthname;

Drop Table Temp;

Drop Field Key;

Now Create a table

Dimension

Name

monthname

Expression

SUM(sales)

Go to presentation tab

Untick suppress zero values.

Anonymous
Not applicable
Author

Thanks for your replay Dada.

It's not working ,Can you please check the attached Qv file and revert back with changed QV if it works fine.

Thanks,

Rajesh

Anonymous
Not applicable
Author

Thanks for replay Manish.

I have checked the application you have shared,in pivot table i need to show if(sum(sales)>0,1,0).I need to show if i select 0 in list box need to only 0 values,if i select 1 only one values,i don't want show sum(sales) in table.

Could you please help on that once?

Thanks,

Rajesh