Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys ,
Please see into excel, Dummy data with desired output. please suggest.
Thanks in Advance
Is this logic Date dependent? What if one status is red in one day and not reported in another date?
yes it is date dependant, if it is RED in any date against ID .i.e Primary key for that, it will be RED.
Then I guess the expected output for C080463 in your sample file is not right. Could you please check and confirm?
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;
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.
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.