I would like to compare the changed meanings in the table which I load and apply the mapping. Which function is better to use? Currently, my code looks like this:
ActivityType: //Create Map with Events
Mapping Load * Inline [
ID, Event
0, Start
1, Change Status
2, Change Progress
3, Change Probability
4, Change Value
5, Change Status and Progress
];
Data: // Load Data
LOAD
CaseID,
Status,
Progress,
Prob,
"Time",
Value
FROM [… data.qvd]
(qvd);
NoConcatenate
// Make Ids
ActivityTypeID:
Load *,
IF(CaseID = Previous(CaseID) AND Status <> Previous(Status),1,
IF(CaseID = Previous(CaseID) AND Progress <> Previous(Progress), 2,
IF(CaseID = Previous(CaseID) AND Prob <> Previous(Prob), 3,
IF(CaseID = Previous(CaseID) AND Value <> Previous(Value), 4,
IF(CaseID = Previous(CaseID) AND Status <> Previous(Status) AND Progress<> Previous(Progress), 5,
0)))))) as ActivityTypeID // add More
Resident Data ORDER BY CaseID desc, "Time" asc;
NoConcatenate // Create Table with Events Final: Load *, ApplyMap('ActivityType', ActivityTypeID, 'No ActivityTypeID') as ActivityType
Resident ActivityTypeID; // Apply the Map
However, I realized that IF checks just the first condition and does not go further. On running the script I do not have values with 5, Change Status and Progress in the new data set .
The syntax looks ok. and also the mapping should work - whereby I would do the mapping within a preceeding load instead of using an extra resident load. This means:
... ActivityTypeID: Load *, ApplyMap('ActivityType', ActivityTypeID, 'No ActivityTypeID') as ActivityType ; Load *, IF(CaseID = Previous(CaseID) AND Status <> Previous(Status),1, ... Resident Data ORDER BY CaseID desc, "Time" asc;
If you don't get the expected values it will be probably caused from the sorting (is Time really a numeric value?) and/or the order and construction of your if-loops - you may need to reverse them and/or checking more than two fields within a single if-loop.
What if I would like to add more variants in the mapping table?
Like:
7, Change Status and Progress 8, Change Status and Probability 9, Change Status and Value
13, Change Progress and Probability 14, Change Progress and Value 15, Change Probability and Value
21, Change Progress and Probability and Value
22, Change Status and Progress and Probability 23, Change Status and Progress and Value 24, Change Status and Probability and Value 25, Change Progress and Probability and Value
26, Change Status and Progress and Probability and Value
Is it OK to still use IF function? May I use switch...case instead?
In this way the conditions would be overlapping. If you apply them in the right order (the one with the most conditions needs to be queried first) it may work whereby with just one field you will lose some kind of granularity because a record of:
Change Status and Progress and Probability and Value
won't be anymore
Change Status and Progress
If this is fine you could use it so - if not you may need multiple fields for it. This means some kind of grouping like in a dimension-table (which might then more suitable as the mapping) of main-group, sub-group, atomic.
Alternatively it should be possible to merge them into a single field by using an as-of-table approach for it but it will be a significantely increase of the complexity.
