topic Re: Compare meanings and apply mapping in QlikView

Compare meanings and apply mapping

Olya — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

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 .

Re: Compare meanings and apply mapping

marcus_sommer — Wed, 21 Aug 2019 11:44:25 GMT

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.

- Marcus

Re: Compare meanings and apply mapping

Olya — Wed, 21 Aug 2019 12:05:54 GMT

Thank you, Marcus.

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?

Re: Compare meanings and apply mapping

Olya — Wed, 21 Aug 2019 12:33:20 GMT

Time is a TimeStamp. It contains data, hours, minutes and seconds.

Re: Compare meanings and apply mapping

marcus_sommer — Wed, 21 Aug 2019 12:35:10 GMT

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.

- Marcus