Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

dmelillo
Contributor II

Flag Based on Selected Period

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:

Supplier201420152016
Supplier1YNY

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

1 Solution

Accepted Solutions

Re: Flag Based on Selected Period

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.


talk is cheap, supply exceeds demand
6 Replies

Re: Flag Based on Selected Period

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


talk is cheap, supply exceeds demand
dmelillo
Contributor II

Re: Flag Based on Selected Period

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

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


talk is cheap, supply exceeds demand
dmelillo
Contributor II

Re: Flag Based on Selected Period

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

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.


talk is cheap, supply exceeds demand
dmelillo
Contributor II

Re: Flag Based on Selected Period

Thank you Gysbert! Appreciate the help.

Community Browser