Hello everyone,
I am trying to come up with a Qlikview solution for the following problem:
I have four columns:
CCID
MID
M State
T State
The MID can have multiple ID which belong to one CCID. I would like to group or classify them in a new field depending on their state. Example: The CCID "55" is entered for multiple MIDs, however, the M State is not closed for all of these IDs. And the T State, which is the state of a different system for this CCID (already merged source) and can only have one state (ie. Msystem has multiple states because there are multiple records; Tsystem has only one record and state), is also different than the M State. I want to highlight these discrepancies for the whole CCID.
Source example
| CCID |
MID |
M State |
T State |
| 55 |
34567 |
Closed |
Open |
| 55 |
34568 |
Closed |
Open |
| 55 |
34569 |
Open |
Open |
| 76 |
53655 |
Open |
Open |
| 58 |
647889 |
Closed |
Open |
| 58 |
14367 |
Closed |
Open |
| 71 |
6354873 |
Closed |
Closed |
| 71 |
284578 |
Closed |
Closed |
| 71 |
47747 |
Closed |
Closed |
| 71 |
36378 |
Closed |
Closed |
I'm not sure how I can do this. I want it to show a simple table, maybe with the CCID, the state and a new Check column which checks if the states are matching. The end goal is for the teams to clean up the discrepancies.
| CCID |
M State final/combined |
Check state |
| 55 |
still open IDs available |
matches T State |
| 76 |
all open |
matches T State |
| 58 |
all closed |
does not match T State |
| 71 |
all closed |
matches T State |
I tried chart tables with different aggr statements aggr(Only({$<CCID={"=Count(Distinct ID)>1"}>}CCID,CCID) and counts and tried to group the CCID in the load code, but it's not combining the multiple MIDs per CCID and state.
Load code is pretty simple and looks like this:
SubmissionData:
Load
...
CCID,
MID,
M State,
T State
From
$(vsource)subs.xlsx
(ooxml, embedded labels);
I would be grateful for any ideas.
I'm using QlikView for Windows Version 12 btw
