Before/After Dimension Categorization

[cbaqir]

I am trying to create a chart that shows where a DEFECT_ID went AFTER it went to COM_CAT = 'ART - Agenda'.

How would I get it to show only if it went to COM_CAT = 'APT - AMB Prioritization Team' AFTER COM_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' AFTER COM_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.

For example, DEFECT_ID = 11830

[NW1965]

Hi

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;

Hope it helps.