Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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
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.
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
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
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;
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