We have a problem in which we have some null values for the field 'Wave'. what we would like to do is detailed below. If there is no ParentCatalogueId we would like the 'Wave' status and 'Score' to remain. However, for CatalogueId 3 where a ParentCatalogueId exists (which never has a 'Wave' status i.e. is always Null), we would like to use CatalogueId 1 instead of 3 and also the corresponding Wave Status.
We can see that the table would work in the same way for CatalogueId 4. A parent CatalogueId exists, (which is 2) so it uses CatalogueId 2, the Wavae Status which is 2 and the score which is 100.
CatalogueId
ParentCatalogueId
Wave
Score
InheritedWave
InheritedScore
1
1
100
2
2
100
3
1
10
1
100
4
2
35
2
100
This is the table we are trying to produce but I'm not sure what way to create the calculated dimensions which would be InheritedWave and InheritedScore. I know there would be some if statements involved but my attempts so far have been unsuccessful.
If anyone has any ideas it would be much appreciated.
Thanks for your reply. This looks as though this could be the solution. However, some of our fields come from different tables. Would you suggest joining the tables beforehand? Wava and CatalogueId (along with a number of other fields) reside in one table and ParentCatalogueId and Score reside in another.
We have three tables (first is a mapping table and other 2 are the data sources) :
Response_Mapping: Mapping Load Distinct (CatalogueID) as CatalogueId, ResponseText; select CatalogueID, ResponseText from dbo.View; REPTEC_QUESTIONNAIREFACT: load Distinct (CatalogueID) as CatalogueId, ParentCatalogueID, ResponseText, If(ISNULL(CopiedFromCatalogueID)=-1,ResponseText,ApplyMap('Response_Mapping',CopiedFromCatalogueID,ResponseText)) as InheritedResponseText; select CatalogueID, ParentCatalogueID, ResponseText, FROM dbo.Questionnaire where ResponseText in ('Compliant','Not Compliant', 'Not Applicable', '');
LtOTable: LOAD CatalogueID as CatalogueId, SystemName as LtOApplication, Wave, FROM [$(vServerName).xls] (biff, embedded labels, table is [LtO Data$]);
The score we mentioned previously is a long expression so we chose to map to responsetext instead which we have done. However to create an inheritedwave field is proving more difficult. We want to say, if there is a ParentCatalogueId use this as the CatalogueID and it's corresponding Wave status if it exists.
