exclude outlets from calculation

[Frank_Hartmann]

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

[puttemans]

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.

[marcus_sommer · Accepted solution]

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

or

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

- Marcus

[datanibbler]

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

[Ralf-Narfeldt]

Example:

RemovedOutlets:

Load * inline [

Outlet

B

D

];

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.

[nikhilgarg]

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

[marcus_sommer]

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

[Ralf-Narfeldt]

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

B

D 

];

Outlets:

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

Load * inline [

Outlet, Value

A,12

B,23

C,45

D,32

];

drop table Removed;

[Frank_Hartmann]

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?

[marcus_sommer]

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