topic exclude outlets from calculation in QlikView

exclude outlets from calculation

Frank_Hartmann — Wed, 11 Mar 2015 13:20:07 GMT

hi qv community,

ich have the following problem:

i have imported an excellist into qlikview! fieldnames= outlet and value

with this data i made my metrics and diagrams in qv. but now i got a second excellist with some outlets which should be excluded from my calculations. how can i prepare my script in order to exclude these outlets?

any ideas

thx for your help

Re: exclude outlets from calculation

puttemans — Wed, 11 Mar 2015 13:26:55 GMT

Hello Frank,

I'd go for an inline load of the list of outlets to exclude, if this list is not too big. This prior to the regular load.

Then on your regular load for the table, you add a 'where not exists' clause.

Re: exclude outlets from calculation

marcus_sommer — Wed, 11 Mar 2015 13:29:04 GMT

I would load the second xls first as a mapping-table and create with:

if(applymap('Map_Excluded', outlet, '#NV') = '#NV', 1, 0) as ExcludedFlag

an additional field in your main-list. Then you could select over this field or include this field in your chart-expression, like:

sum(value) * ExcludedFlag

sum({< ExcludedFlag = {1}>} value)

- Marcus

Re: exclude outlets from calculation

datanibbler — Wed, 11 Mar 2015 13:30:21 GMT

Hi Frank,

just load that "exclusion_list" first and then use a WHERE NOT EXISTS() clause in the LOAD for the other list. Look it up in the help_file.

=> That way, only those values will be loaded which are not present in the previously loaded "exclusion_list".

HTH

Re: exclude outlets from calculation

Ralf-Narfeldt — Wed, 11 Mar 2015 13:36:40 GMT

Example:

RemovedOutlets:

Load * inline [

Outlet

];

Outlets:

Load * inline [

Outlet, Value

A,12

B,23

C,45

D,32

] where Not Exists(Outlet);

drop table RemovedOutlets;

Replace the two loads with your real load statements for the Excel files but keep the "where Not Exists(Outlet)" clause.

Re: exclude outlets from calculation

nikhilgarg — Wed, 11 Mar 2015 13:43:35 GMT

But if i am not wrong then in this case we have to join both the tables either with join or concatenate. Isn't it??

and also in that case too would we be able to exclude the specified outlets??

Plz tell

thanks

Re: exclude outlets from calculation

marcus_sommer — Wed, 11 Mar 2015 13:43:58 GMT

A comment to the suggestions about removing the data with a where-clause - these excluded data aren't available within the app - if you need these data for other calculations or to flexible switch and check the results you need an approach to keep all data within the app and identify them, like above mentioned.

- Marcus

Re: exclude outlets from calculation

Ralf-Narfeldt — Wed, 11 Mar 2015 13:52:40 GMT

This would be an alternative if you want to keep the data for removed outlets.

You'd get a field ExistingOutlet that is True for existing Outlet and False for removed Outlets.

Removed:

Load * inline [

Outlet

];

Outlets:

Load *, If(Exists(Outlet), False(), True()) As ExistingOutlet;

Load * inline [

Outlet, Value

A,12

B,23

C,45

D,32

];

drop table Removed;

Re: exclude outlets from calculation

Frank_Hartmann — Wed, 11 Mar 2015 14:07:32 GMT

Hi marcus, thank you for your answer:

i tried like you said, but when running the script QV gives the message: Generic tables shoud have three fields

Here is how my script looks like:

Excluded_Outlets:
Mapping LOAD AWS_Recherchen as excluded_outlets
FROM
[AuszuschließendeBetriebe.xlsx]
(ooxml, embedded labels, table is Tabelle1);

QVD_Recherchen:
LOAD AWS_Recherchen,
     Datum,
     'AK_' & date(makeweekdate(left(Datum, 4), subfield(Datum, ' ', -1), 0),'YYYYMMDD') AS Referal_Date,
     Daten_Recherchen,
     if(applymap('Excluded_Outlets', AWS_Recherchen, '#NV') = '#NV', 1, 0) as ExcludedFlag
FROM
[RecherchenTest.qvd]
(qvd);

What am i doing wrong?

Re: exclude outlets from calculation

marcus_sommer — Wed, 11 Mar 2015 14:15:47 GMT

A mapping table need always two fields - for this you could simply duplicate this field:

Excluded_Outlets:
Mapping LOAD AWS_Recherchen, AWS_Recherchen as excluded_outlets
FROM
[AuszuschließendeBetriebe.xlsx]
(ooxml, embedded labels, table is Tabelle1);

- Marcus

Re: exclude outlets from calculation

Frank_Hartmann — Wed, 11 Mar 2015 14:21:22 GMT

Marcus, thank you very very much, now it works