ID 1 has one CHDLM with X and one without X.
I wanna add a new dimension 'Comments' .
In the comments dimension, i will have 'X category' for ID1.
In the final resident load, i will just take Load * Resident where Right(CHDLM, 1) <> 'X,
So, i will have only CHDLM without 'X' but in the category, i can see that the ID 1 was of 'X' Category.
LOAD * INLINE [
LOAD Count(ID) as X_Count,
Where Len(Trim(KeepChar(CHDLM, 'X'))) > 0
Group By ID;
If(X_Count > 0, 'Had X') as Category
Where Len(Trim(KeepChar(CHDLM, 'X'))) = 0;
DROP Table Table;
testtt.qvw 149.2 K