topic 0,1 should be selectable through list box in QlikView

0,1 should be selectable through list box

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: 0,1 should be selectable through list box

adamdavi3s — Mon, 16 Jan 2017 07:17:10 GMT

Hi,

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

Generating Missing Data In QlikView

Re: 0,1 should be selectable through list box

Anonymous — Mon, 16 Jan 2017 07:22:21 GMT

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

Re: 0,1 should be selectable through list box

khalander — Mon, 16 Jan 2017 07:25:49 GMT

Hi,

Create one inline table like below

LOAD * INLINE [

Selection filter

];

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.

Re: 0,1 should be selectable through list box

khalander — Mon, 16 Jan 2017 07:26:26 GMT

=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

Re: 0,1 should be selectable through list box

adamdavi3s — Mon, 16 Jan 2017 07:26:58 GMT

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

Re: 0,1 should be selectable through list box

adamdavi3s — Mon, 16 Jan 2017 07:27:24 GMT

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

Re: 0,1 should be selectable through list box

MK_QSL — Mon, 16 Jan 2017 07:29:58 GMT

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.

Re: 0,1 should be selectable through list box

Anonymous — Mon, 16 Jan 2017 07:30:11 GMT

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

Re: 0,1 should be selectable through list box

Anonymous — Mon, 16 Jan 2017 07:34:33 GMT

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

Re: 0,1 should be selectable through list box

Anonymous — Mon, 16 Jan 2017 07:35:54 GMT

The data is not null ,it is missing that's why i am not able use isnull() function

Re: 0,1 should be selectable through list box

Anonymous — Mon, 16 Jan 2017 07:36:55 GMT

Yes correct adam..data not contains nulls ,data has missing values,due to that not able to replace with nulls.

Thanks,

Rajesh

Re: 0,1 should be selectable through list box

khalander — Mon, 16 Jan 2017 08:32:25 GMT

Hi,

uncheck the option suppress zero values in presentation. and write expression like below

=if([Selection filter]=0,

if(Sum(sales)=0,0,sum(sales)),

if(Sum(sales)=0,1,sum(sales)))

Re: 0,1 should be selectable through list box

MK_QSL — Mon, 16 Jan 2017 08:53:07 GMT

OK then change the expression as below

IF(SUM(sales)>0,1,0)

and you can't get the same in Pivot table.

You have to convert to straight table.

check enclosed file..

Re: 0,1 should be selectable through list box

Anonymous — Mon, 16 Jan 2017 09:09:52 GMT

Hi I have checked Manish..i need to show month as row,means i need to select pivot table only..in stright table missing values won't be display when u put month as column.

Thanks,

Rajesh

Re: 0,1 should be selectable through list box

MK_QSL — Mon, 16 Jan 2017 09:12:45 GMT

No way you can get the desired result in pivot table.

Re: 0,1 should be selectable through list box

khalander — Mon, 16 Jan 2017 10:58:26 GMT

Hi Rajesh,

first create excel sheet with two fileds with Name and monthname and fill the data like

Name,monthname

Rajesh Jan

Rajesh Feb

Rajesh Dec

for all users.

Then use the below script

Name_mapping:

LOAD

Name,

monthname

FROM

[..\New Microsoft Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

Join

LOAD * 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

];

Data:

LOAD *,

Name&monthname as key

Resident Name_mapping;

LOAD

Name&monthname as key,

if(IsNull(sales)<0,0,1) as flag

Resident Name_mapping;

DROP Table Name_mapping;

Output:

Re: 0,1 should be selectable through list box

MK_QSL — Mon, 16 Jan 2017 11:05:21 GMT

When you click on 1.. is that giving you all Name and monthname with flag 1 only?

Re: 0,1 should be selectable through list box

khalander — Mon, 16 Jan 2017 11:31:05 GMT

yes Manish,

PFB screenshot

Re: 0,1 should be selectable through list box

MK_QSL — Mon, 16 Jan 2017 11:45:30 GMT

This is exactly the same which I have provided.

Try to keep monthname as column and let me know whether it's working or not.