Search based on common value within a field

[#1]

I'm creating a television programming tool in Qlikview for a movie channel, which has the broadcast rights to movies during certain time periods. These time periods are called "availability windows". In Qlikview, I've modeled the data to look like this:

Availability Window

-----------------------

Window ID

Movie

Availability Start (MM/DD/YYYY format)

Availability End (MM/DD/YYYY format)

Let's say I have 2 movies with 3 different availability windows each:

ID	Movie	Availability Start	Availability End
1	Fury	03/01/2016	03/31/2016
2	Fury	05/01/2016	05/15/2016
3	Fury	08/01/2016	10/31/2016
4	Jurassic World	04/01/2016	05/15/2016
5	Jurassic World	09/01/2016	09/15/2016
6	Jurassic World	12/01/2016	12/31/2016
7	American Sniper	01/01/2016	02/28/2016
8	American Sniper	06/01/2016	06/15/2016
9	American Sniper	12/01/2016	12/31/2016

In the UI, I'll have a two date pickers tied to two variables: vDateStart, vDateEnd. I'll also have a listbox with two entries in it: "First time availability" and "Last time availability".

"First time availability" should search for movies with an availability window that overlaps the time within vDateStart and vDateEnd but also doesn't have any availability windows that end BEFORE vDateStart.

"Last time availability" should search for movies with an availability window that overlaps the time within vDateStart and vDateEnd but also doesn't have any availability windows AFTER vDateEnd.

So let's say the user selects vDateStart as 03/21/31 and vDateEnd as 06/21/31 and selects first time availability. Qlikview should display the availability windows with IDs 1 and 4.

If the user selects vDateStart as 09/01/2016 and vDateEnd as 09/31/2016 and selects last time availability, Qlikview should only display ID 3 since the last availability window for Fury expires within that date range. It won't show ID 5 because Jurassic World has an availability window after that time range between 12/1/2016 and 12/31/2016.

I couldn't even begin to think of the expression in Qlikview to get what I need. Please help!

[sinanozdemir]

Hi Jay,

I think I am getting closer to what you need:

Although, when I type 03/21/2016 and 06/21/2016 and choose "First Time Availability", I am pulling IDs 1,2, and 4:

I placed the expression to pull IDs, but I did it quickly so I am not sure if it works for other scenarios:

I am attaching qvw in case it is what you are looking for.

Hope this helps.

[#3]

Hey, Sinan. I clicked on Last Availability and I wasn't getting any results. Did you make any changes after that screenshot?

Edit: Also, I changed the vDateStart to 04/01/2016 and it should not have shown Fury 05/01/2016-05/15/2016 because there was a previous window for Fury between 03/01/2016 and 03/31/2016.

[sinanozdemir]

Hey Jay,

The original qvw on my work pc so I won't be able to get to it till tomorrow. I will take a look at it.

[sinanozdemir]

Hey Jay,

So can you give me some examples?

If vDateStart = 04/01/2016 and vDateEnd = 06/21/2016, what movies should appear when first time availability and last time availability are chosen?

Thanks

[#6]

I've added an additional movie to give you a better idea of the logic requirement.

Assuming vDateStart is 04/01/2016 and vDateEnd=06/21/2016:

If first time availability is chosen, ID 4 should selected.

If last time availability is chosen, no IDs should be selected because all 3 movies have an availability window past that date range.

Now let's assume vDateStart is 09/01/2016 and vDateEnd is 09/31/2016:

If first time availability is chosen, no IDs should be selected because all 3 movies have an availability window before that range.

If last time availability is chosen, ID 3 should be selected because Fury doesn't have any availability window that begins beyond that date range while American Sniper and Jurassic world both have an availability window for December.

[sinanozdemir]

Thanks Jay. Let me work on this and hopefully I will have an answer for you soon.

[ChiragPradhan]

Hi Jay

Find attached a a test qvw. See if it works for you.

Regards

Chirag

[sinanozdemir · Accepted solution]

Hey Jay,

Hopefully, the attached should do it. I cannot open what ChiragPradhan attached. However, here is my solution:

Let me know if this works for you.

Thanks.

[#10]

This is exactly what I need. Thank you!