Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

abhaysingh
Contributor III

Logic transformation Help

Hi Guys ,

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

Thanks in Advance

6 Replies
MVP
MVP

Re: Logic transformation Help

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

abhaysingh
Contributor III

Re: Logic transformation Help

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

MVP
MVP

Re: Logic transformation Help

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

vinieme12
Esteemed Contributor II

Re: Logic transformation Help

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
Valued Contributor

Re: Logic transformation Help

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
Contributor III

Re: Logic transformation Help

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.

Community Browser