Logic transformation Help

[abhaysingh]

Hi Guys ,

Please see into excel, Dummy data with desired output. please suggest.

Thanks in Advance

[tresesco]

Is this logic Date dependent? What if one status is red in one day and not reported in another date?

[abhaysingh]

yes it is date dependant, if it is RED in any date against ID .i.e Primary key for that, it will be RED.

[tresesco]

Then I guess the expected output for C080463 in your sample file is not right. Could you please check and confirm?

[vinieme12]

Try like below

FACT:

LOAD ID,

     dual(RAGStatus,Match(RAGStatus,'Green','NotReported','Red')) as RAGStatus,

     Date,

     AutoNumber(ID&Date) as ID_DT_Grp

FROM

[Excelfilepath]

(ooxml, embedded labels, table is Sheet1);

left join(FACT)

load ID_DT_Grp, Pick(Max(RAGStatus) ,'Green','NotReported','Red') as OUTPUT

Resident FACT

Group by ID_DT_Grp;

[kavita25]

I think there might be slight change as follows:

FACT:

LOAD ID,

dual(RAGStatus,Match(RAGStatus, 'NotReported','Green' ,'Red')) as RAGStatus,

     Date,

     AutoNumber(ID&Date) as ID_DT_Grp

FROM

[Excelfilepath]

(ooxml, embedded labels, table is Sheet1);

left join(FACT)

load ID_DT_Grp, Pick(Max(RAGStatus) , 'NotReported','Green' ,'Red') as OUTPUT

Resident FACT

Group by ID_DT_Grp;

As the above code..gives Green & not reported as Not Reported.. You want Green & Not Reported as Green.

[abhaysingh]

I tried in this way

[Sheet1]:

LOAD [ID],

  //[F2],

  [RAGStatus],

  [Date]

// [F5],

// [Output required],

// [F7],

// [Remark]

FROM [lib://AttachedFiles/Dummy Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

  left join

load [ID],

  [Date],

only(if(RAGStatus= 'Red','Red')) as test,

only(if(RAGStatus= 'Green','Green')) as test1,

only(if(RAGStatus= 'NotReported','NotReported')) as test2

Resident Sheet1 Group by [ID],[Date];

and at front end

if(not IsNull(test),test,

if(not IsNull(test1),test1,

if(not IsNull(test2),test2)))

working fine.