topic Re: Filtering rows without remove from the table in QlikView

Filtering rows without remove from the table

Greg3 — Mon, 24 Apr 2023 15:05:47 GMT

Hi,

I am quite a new in Qlik, and I have an issue which I can't solve yet, so I would like to ask help from more experts people.

I try to explain the simplest way I can.

I only want presenting Agencies (by Index number - Index_AgencyID the field name) who has 0 bookings in the selected year(s).

I use this as a solution:
=aggr(only({$<[Index_AgencyID]={count(Index_BookingsID)=0"}>} [Index_AgencyID]), [Index_AgencyID])

But unfortunately with this, I exclude every Agency with bookings, and I only want to exclude those agencies who don't have bookings for the selected year(s). So maybe 1 agency only has 1 booking in 2020, so in that case if I choose 2021, I want to see it in the table, but if I choose 2020 then I don't want to.

Thank you in advance!

Re: Filtering rows without remove from the table

Kushal_Chawda — Mon, 24 Apr 2023 21:27:00 GMT

@Greg3 could you share sample data with expected output?

Re: Filtering rows without remove from the table

Greg3 — Tue, 25 Apr 2023 08:32:42 GMT

@Kushal_Chawda

I hope it's enough for the solution, anyway thanks for the interest!

Re: Filtering rows without remove from the table

Kushal_Chawda — Tue, 25 Apr 2023 12:19:10 GMT

@Greg3 what if one agency has booking in year 2021 and don't have any booking in 2022. If you select 2021 and 2022 , would you still like to see that agency?

Re: Filtering rows without remove from the table

Greg3 — Tue, 25 Apr 2023 12:40:05 GMT

@Kushal_Chawda In that case I don't want to see it, just only if I choose 2022, Of course if it's possible to present agencies this way.

Re: Filtering rows without remove from the table

Kushal_Chawda — Tue, 25 Apr 2023 14:12:10 GMT

@Greg3 try below expression for Bookings. Make Sure Include Zero value option checked

Sum({<[Index_AgencyID]={ "=sum(Bookings)=0"}>}Bookings)

Re: Filtering rows without remove from the table

Qrishna — Tue, 25 Apr 2023 14:22:31 GMT

Tmp:
Load * Inline [
Index_AgencyID , Bookings, Year
A, 3, 2018
A, 0, 2019
A, 0, 2020
A, 1, 2021
A, 3, 2022
A, 5, 2023
B, 2, 2016
B, 0, 2018
B, 1, 2019
B, 3, 2020
B, 0, 2021
B, 0, 2022
B, 7, 2023 ];

Year:
Load distinct Year
Resident Tmp;

NoConcatenate
Out:
Load *
resident Tmp
where Bookings > 0;

Qualify *;
Unqualify Year;
Data:
Load *
Resident Tmp;
drop table Tmp;

Re: Filtering rows without remove from the table

Greg3 — Tue, 25 Apr 2023 14:54:31 GMT

@Kushal_Chawda Thanks for the advice! I tried it, unfortunately it's just put zeros for the column "Bookings". I checked one of the agency and they have bookings for that specific year I choose.

Could you please maybe advise something else ?

Sorry if I ask a difficult question, sadly I don't have access for the Database, and can't create script, just only chart functiont like Set analysis, FYI.

Re: Filtering rows without remove from the table

Greg3 — Tue, 25 Apr 2023 14:57:06 GMT

@Qrishna Thanks for the interests, I am not sure what you want to say with this, sorry. I don't have access to the Database and I can only creating chart functions, if I saying it right.

Re: Filtering rows without remove from the table

Kushal_Chawda — Tue, 25 Apr 2023 15:06:27 GMT

@Greg3 My logic will show only zero booking for the year you have selected, so you will get zero values for the bookings. Is it not you want? In your screenshot, you have shown zero in Bookings when year is selected.

Re: Filtering rows without remove from the table

Greg3 — Wed, 26 Apr 2023 10:34:35 GMT

@Kushal_Chawda Here the first table is where I use what you suggested, at the second I don't use nothing. You can see the differences.

I don't want to modify the numbers to presenting at every agency has 0 bookings.
For example, you can see in the second table AgencyID 7, it has 0 bookings for 2023 (this is the year I selected) but maybe if I choose 2022 it would disappear because they have at least 1 booking for 2022. This is what I want.

I used this originally : =aggr(only({$<[Index_AgencyID]={"=count(Index_BookingsID)=0"}>} [Index_AgencyID]), [Index_AgencyID])

But it removes every agency with bookings for the 5 years (2019-2023) but I don't want this. I want that if I select a year or years, the agencies for that period that have 0 bookings should be displayed. Do not display agencies that have bookings for the given period.

Do you have any idea?

Thank you for your helping!

Re: Filtering rows without remove from the table

Kushal_Chawda — Wed, 26 Apr 2023 11:37:55 GMT

@Greg3 Not sure what expression you are using and how underlying data is but with expression I provided AgencyID 19 should not have included in the report because it has bookings for the selected year.

see the example below. I have created dummy data

Data:
Load * Inline [
AgencyID, Bookings, Year
7,0,2023
8,0,2023
19,18,2023
7,10,2022];

Now, when I select 2023, I get only 7,8 because in that year both has zero

Now, when I select both 2022,2023. I only get 8 because it has 0 bookings for the selected period. 7 has booking in 2022 so it is not included.

Re: Filtering rows without remove from the table

Greg3 — Wed, 26 Apr 2023 15:04:05 GMT

@Kushal_Chawda So I think the issue should come from my data or something like that.

My booking numbers are displayed via a variable called "vAllBookings" and before my task the bookings were presented like this

=$(vAllBookings)

And the variable equal with this function, this is behind the variable.

Sum(Index_BookingsID_All)

Fortunately I can create and modify variables.

I can't get what is that Index_BookingsID_ALL , it looks like this when I disaplayed it by itself, and next to it on the left side I using the vAllBookings

I used what you suggested like this :

aggr(Sum({<[Index_AgencyID]={ "=sum(Index_BookingsID_All)=0"}>} $(vAllBookings)),$(vAllBookings))

And it looks like this in the table

But I checked randomly picked Agencies, and they have bookings for 2023 (this is the selected year)

I tried like this too

=Sum({<[Index_AgencyID]={ "=Sum(Index_BookingsID_All)=0"}>} Index_BookingsID_All)

And it looks like this, I put at the last column the vAllBookings, just to see that sadly it's not good

Please any suggestion ?

Thanks in advance!

Re: Filtering rows without remove from the table

Kushal_Chawda — Wed, 26 Apr 2023 15:15:24 GMT

@Greg3 I think you just need Bookings column with below expression. Keep AgencyID, Searches & Bookings. Remove other column..

=Sum({<[Index_AgencyID]={ "=Sum(Index_BookingsID_All)=0"}>} Index_BookingsID_All)