Discussion board where members can get started with Qlik Sense.
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
dual(RAGStatus,Match(RAGStatus,'Green','NotReported','Red')) as RAGStatus,
AutoNumber(ID&Date) as ID_DT_Grp
(ooxml, embedded labels, table is Sheet1);
load ID_DT_Grp, Pick(Max(RAGStatus) ,'Green','NotReported','Red') as OUTPUT
Group by ID_DT_Grp;
I think there might be slight change as follows:
dual(RAGStatus,Match(RAGStatus, 'NotReported','Green' ,'Red')) as RAGStatus,
load ID_DT_Grp, Pick(Max(RAGStatus) , 'NotReported','Green' ,'Red') as OUTPUT
As the above code..gives Green & not reported as Not Reported.. You want Green & Not Reported as Green.
I tried in this way
// [Output required],
FROM [lib://AttachedFiles/Dummy Data.xlsx]
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