topic Re: Excluding values from table in QlikView

Excluding values from table

rassanbekov — Fri, 09 Jun 2017 08:49:00 GMT

Hello!

In straight I need to show only those value which have "zero" in cells. For example,

Header 1	Header 2	Header 3	Header 4
A	10	0	12
B	20	0	0
C	30	20	11
D	40	10	0
E	50	0	0

As a result of formula, the table should transform to the following:

Header 1	Header 2	Header 3	Header 4
B	20	0	0
E	50	0	0

Kind regards,

Ruslan

Re: Excluding values from table

prma7799 — Fri, 09 Jun 2017 08:55:14 GMT

From where you want this back end or in front end?

Re: Excluding values from table

kamal_sanguri — Fri, 09 Jun 2017 09:00:32 GMT

Do it at script level, create a flag to see where we have all zeroes and then create a calculated dimension using that flag.

Refer attached.

Re: Excluding values from table

kamal_sanguri — Fri, 09 Jun 2017 09:01:15 GMT

Re: Excluding values from table

rassanbekov — Fri, 09 Jun 2017 09:18:54 GMT

Where I should insert the code in script because flagging seems doesn't work.

Directory;

LOAD Index,

[Company name],

Header 3,

FROM [0_ISS\Processed\ProcPopulation 31.12.2016.xlsx] (ooxml, embedded labels, table is Sheet1);

Directory;

LOAD Index,

Header 4,

FROM [0_ISS\Processed\ProcPopulation 01.01.2017-20.05.2017.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD

If([CR, Оплачено]=0 and Оплачено=0,0,1) as Flag;

Re: Excluding values from table

prma7799 — Fri, 09 Jun 2017 09:22:48 GMT

Directory;

Load * , If(Header3=0 ,0,1) as Flag

LOAD Index,

[Company name],

Header 3,

FROM [0_ISS\Processed\ProcPopulation 31.12.2016.xlsx] (ooxml, embedded labels, table is Sheet1);

Directory;

Load * , If(Header4=0,0,1) as Flag

LOAD Index,

Header 4,

FROM [0_ISS\Processed\ProcPopulation 01.01.2017-20.05.2017.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Excluding values from table

kamal_sanguri — Fri, 09 Jun 2017 09:25:32 GMT

You can also do it at the front end-

Just use a calculated dimension-

=If(Header3=0 and Header4= 0,Header1,NULL())

Re: Excluding values from table

kamal_sanguri — Fri, 09 Jun 2017 09:26:31 GMT

Refer this. Without flagging at Front end.

Re: Excluding values from table

kamal_sanguri — Fri, 09 Jun 2017 09:28:56 GMT

Also, suppress the NULLS

Re: Excluding values from table

shanky1907 — Fri, 09 Jun 2017 09:42:42 GMT

Are you making a new table after these two loads for Header3 & Header4 as zero?

Re: Excluding values from table

Anil_Babu_Samineni — Fri, 09 Jun 2017 09:47:25 GMT

Why Can't this?

LOAD [Header 1],

[Header 2],

[Header 3],

[Header 4]

FROM

[https://community.qlik.com/message/1284642]

(html, codepage is 1252, embedded labels, table is @1) Where Match([Header 3], 0) and Match([Header 4], 0);