how to write aggr script with Flag variables

[ashmitp869]

Hi there,

I have a fact table which is linked dimset (1 to n) which is again linked to dimbridge (1 to n)  again linked to dim (1 to n)

How to aggr it with one entry for creating a Flag, scritp is provided below -

Tried this script to make one entry for key SE_CBK_SK but not working.need to group it by Dignosis . Please assist with the script.

tmpCovidFlag:
NoConcatenate
LOAD
[SE_CBK_SK],
[DIMSET_DIAGNOSIS_SK] as [CovidFlag.DIMSET_DIAGNOSIS_SK]
RESIDENT [fact];



INNER JOIN(tmpCovidFlag)
LOAD
     [DIMSET_DIAGNOSIS_SK] as [CovidFlag.DIMSET_DIAGNOSIS_SK],
     [B_DIMSET_DIAGNOSIS_SK] as [CovidFlag.B_DIMSET_DIAGNOSIS_SK]  
RESIDENT [v_DIMSET_DIAGNOSIS];

INNER JOIN(tmpCovidFlag)
LOAD
     [B_DIMSET_DIAGNOSIS_SK] as [CovidFlag.B_DIMSET_DIAGNOSIS_SK]  
     ,[DIM_DIAGNOSIS_SK] as [CovidFlag.DIM_DIAGNOSIS_SK]
RESIDENT [v_DIMBRIDGE_DIAGNOSIS];

INNER JOIN(tmpCovidFlag)
LOAD
     [DIM_DIAGNOSIS_SK] as [CovidFlag.DIM_DIAGNOSIS_SK]
     ,[Diagnosis Code] as   [CovidFlag.Diagnosis Code]
RESIDENT [v_DIM_DIAGNOSIS];


COVID_Flags:
Load
Distinct [SE_CBK_SK],
'COVID Patient' as [COVID-19 Diagnosis flag]
resident tmpCovidFlag
where match(trim([CovidFlag.Diagnosis Code]),'1', '2');

DROP TABLE tmpCovidFlag;
 

Hope the DM screenshot will help to understand the requirement -