Dear Qlik Sense community,
I am new to Qlik Sense and it seems that Qlik works great to visualize certain applications. However, I am stuck with a certain task and I can't find a proper solution to it. I hope you guys can help me out.
In short: I have the task to visualize the quant scores from a file called "QuantScores" (Columns: Valuation and Revisions) as a scatter plot, but only visualize those quant scores in which a mutation at a particular date has found place. The mutations can be found in a different file called "Mutations". The difficulty is to visualize only the relevant quant scores.
For example, when a mutation has found place on 21-06-2016, we want to find the nearest QuantScores file just before this date, let's say we have one at 20-06-2016. From this QuantScores file we only want to visualize the Valuation and Revisions scores from the relevant stocks. That is, the stocks that are named in the Mutation file with the date 21-06-2016 and not from the remaining stocks.
We want to do this for all the different dates in the Mutation file. In that way we can create a scatter plot giving a quality overview of the relevant Valuation and Revision scores from the stocks that have been traded.
-> The QuantScores files contain only 3 relevant columns. Namely a column containing the name of a stock and the other two columns contain the valuation and revision score. Furthermore, the QuantScores files are all named as follows "QuantScores 20160920" showing the date of when the file was published.
-> The Mutation file contains only 2 relevant columns. Namely a column containing the date of the mutation of a certain stock (e.g. buying or selling of a stock are considered a mutation) and a column containing the corresponding names of the stocks for those date.
I have managed to extract the dates from the QuantScores file as follows:
Date(Date#(LEFT(right(Filename(),13),8),'YYYYMMDD'),'DD-MM-YYYY') AS "QuantScores Date",
This gives us a list of dates in the same order as defined in the Mutation file.
Question: How can I link the Mutation date to the correct QuantScore date such that it selects only the relevant Valuation and Revision Score from the correct QuantScore file?
I originally taught that the issue could be solved with the function Applymap, but this solely gives a column of dates without selecting the relevant values. In other words, it still shows the uninteresting stocks which we would like to exclude.
Please feel free to let me know if something is unclear to you. Thank you very much in advance for all the help.
