I am trying to create a chart that shows where a DEFECT_ID went AFTERit went to COM_CAT = 'ART - Agenda'.
How would I get it to show only if it went to COM_CAT = 'APT - AMB Prioritization Team' AFTERCOM_CAT_START_DATE where COM_CAT = 'ART - Agenda'?
For example, DEFECT_ID 11297.
Similarly, how would I get it to show if it went BACKWARDS so COM_CAT = 'Regional Clinical Informatics' AFTERCOM_CAT_START_DATE where COM_CAT = 'ART - Agenda'? I don't want those to show if they hit COM_CAT = 'Regional Clinical Informatics' BEFORE COM_CAT = 'ART - Agenda'. The idea is to capture those that needed rework.
I can't download your sample QVW because my company doesn't allow downloads from the community website, but I created some dummy data by creating a table in excel with an ID, DATE and CETEGORY.
The following script uses sorting and mapping functions to create a final table that has your category AND a new dimension value called Next Category, which you should then be able to use in your charts in expressions.
/* Load the data */ TmpData: LOAD ID , DATE , CATEGORY FROM [Z:\Test.xlsx] (ooxml, embedded labels, table is Sheet1);
/* Now load the data and sort it, and add a rownumber to each row of data */ SortedData: LOAD * , RowNo() AS RowNumber RESIDENT TmpData ORDER BY ID , DATE;
/* Drop the first table */ DROP TABLE TmpData;
/* Build a mapping table using the ID & Rownumber as the key value and the category as the value returned */ NextValueMap: MAPPING LOAD ID&RowNumber AS KeyVal , CATEGORY AS Value RESIDENT SortedData;
/* Now load the final table, this uses an applymap function to get the Next Category, note that you will need to make sure that you have the ability to sort values appropriately for this to work this assumes clean data in terms of ability to sort */ Data: LOAD ID , DATE , CATEGORY , RowNumber , ApplyMap('NextValueMap',ID&RowNumber+1,'No Next Record') AS NextCategory RESIDENT SortedData;
/* Drop the sorted data table, leaving just the Data table in memory to work with */ DROP TABLE SortedData;