topic Re: Flag Based on Selected Period in QlikView

Flag Based on Selected Period

Anonymous — Fri, 12 Feb 2016 17:49:48 GMT

My issues is that I have preferred suppliers that change from year to year. I want to be able to report on preferred supplier spend depending on the year selected by the end user.

Right now my approach is to create an apply map based on the table below:

Supplier	2014	2015	2016
Supplier1	Y	N	Y

My idea would be to create three different fields ... i.e Preferred 2014 Flag, Preferred 2015 Flag, Preferred 2016 .. but there are many issues that come with this approach and I am not sure it is going to work.

Does anyone have another suggestion? The end goal would be for Supplier1's spend to be calculated under preferred for 2014 and 2016, but not 2015.

Thank you,

- dave

Re: Flag Based on Selected Period

Gysbert_Wassenaar — Fri, 12 Feb 2016 18:09:16 GMT

I suggest you post a small qlikview document that illustrates the problem.

Re: Flag Based on Selected Period

Anonymous — Fri, 12 Feb 2016 18:19:32 GMT

Gysbert - Please see attached. These is an extremely simplified version, but it represents the issue.

In my real data model I have one fact table, and one calendar table. If I introduce the preferred data this way I fear I will have multiple synthetic keys. I also believe there is a more sophisticated approach I am missing.

Any help is appreciated.

Re: Flag Based on Selected Period

Gysbert_Wassenaar — Fri, 12 Feb 2016 18:27:21 GMT

Does this do what you want: sum({<Preferred={'Y'}>}Spend)

Re: Flag Based on Selected Period

Anonymous — Fri, 12 Feb 2016 18:28:56 GMT

Yes. That set analysis does what I want, but I am more concerned with the overall approach I am taking. The data sets I am working with a much larger, and I want to make sure I do not have any synthetic keys.

I am just overthinking this?

Re: Flag Based on Selected Period

Gysbert_Wassenaar — Fri, 12 Feb 2016 18:36:16 GMT

The synthetic key in your example is no problem and shouldn't be with large data sets. I don't think you need to remove it. But you could merge the Preferred table with the Fact table with a join or a mapping table+applymap. You can also replace the Y/N values with dual (Y,1) (N,0) values so you can multiply them with the Spend values.

Re: Flag Based on Selected Period

Anonymous — Fri, 12 Feb 2016 18:37:55 GMT

Thank you Gysbert! Appreciate the help.