Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
This seems like it should be straightforward, but I can't get it to work...
Within my large dataset, each row has a 'CHierarchy' and 'THierarchy' numerical field.
I want to count each case where CHierarchy - THierarchy = 1.
I've tried:
COUNT(CHierarchy-THierarchy=1)
COUNT({<CHierarchy-THierarchy={1}>})
Then in desperation created a new field called 'CaseCounter' (with a value of '1' on each row), and tried:
COUNT({<CHierarchy-THierarchy={1}>} CaseCounter)
But nothing has worked so far.
Any help much appreciated.
You can also add a new field in your script so that you do not need an if statement in your expression.
(CHierarchy - THierarchy) as diffHierarchy
Than do
Count({<diffHierarchy={1}>} CaseCounter)
or
Sum({<diffHierarchy={1}>} 1)
Try
COUNT( If( CHierarchy-THierarchy=1, 1, NULL() ))
or
SUM( If( CHierarchy-THierarchy=1, 1))
Or create a field in the script with above if() statement, then count / sum this field.
You will need a unique field here to do it using set analysis:
Sum({<UniqueField = {"=(CHierarchy - THierarchy) = 1"}>} 1)
or try using if statement:
Sum(If((CHierarchy - THierarchy) = 1, 1))
You can also add a new field in your script so that you do not need an if statement in your expression.
(CHierarchy - THierarchy) as diffHierarchy
Than do
Count({<diffHierarchy={1}>} CaseCounter)
or
Sum({<diffHierarchy={1}>} 1)
I like the idea of creating the new field in script to avoid having too many if statements.
However, I did try this before but I'm having trouble because the two fields are in different tables.
JOIN [Table MP1]:
[Table Target_Grade]:
LOAD SubjectType,
Actual_TargetGrade,
TGradePoints_New,
TGradePoints_Old,
THierarchy
FROM
[\\L...\Grade map for QV.xls]
(biff, embedded labels, table is [Target points$]);
JOIN [Table MP1]:
LOAD SubjectType,
Actual_CurrentGrade,
CGradePoints_New,
CGradePoints_Old,
CHierarchy,
CHierarchy-Lookup('THierarchy','SubjectType',SubjectType,'Table Target_Grade') as Grade_Difference
FROM
[\\L...\Grade map for QV.xls]
(biff, embedded labels, table is [Current points$]);
The last line of script is clearly where I'm going wrong, but I'm not sure why...
Are you sure that table 'Table Target_Grade' does exist when you call the Lookup() function? In above script snippet, the first LOAD is JOINed to [Table MP1], so this won't create a table [Table Target_Grade].
Ah, ok - that's good to know.
Have changed the Lookup to 'Table MP1', but still doesn't seem to be working. It's not throwing up any errors, but the field I have for 'Grade_Difference' remains blank...
I've worked out why it isn't working at the moment:
CHierarchy and THierarchy are identical within the tables, so CHierarchy-THierarchy in the script, always gives a value of '0'.
However, if I do 'CHierarcy-THierarchy' as an expression, with 'Person ID' as a dimension, it works (as each person will have a different CHierarchy score and THierarchy score). So I need to find a way to calculate this new field in the script for every row in 'Table MP1'.
Please use below expression
Sum({<SubjectType= {"=(CHierarchy - THierarchy) = 1"}>} 1)
Thanks for all suggestions so far - unfortunately the 'COUNT/SUM( If( CHierarchy-THierarchy=1, 1, NULL() ))' type suggestions aren't working. Each gives me a different total that doesn't match what I'm expecting to see.
I'm sure it's an error I'm making somewhere, but I'm not sure where it is yet. Creating a new field with the grade difference seems like my best bet at the moment, but still not getting anywhere with that either...
Will keep trying and post on here when the correct solution comes up.