How to compare and combine the data?

[varunreddy]

Hi All,

I have two fields Date and Status. For each date I have multiple statuses. Based on those multiple statuses I want to create one value for each date.

Input data:

Date	Status
Jun-19	Submitted
Jun-19	Partially Submitted
May-19	Submitted
May-19	Submitted
Apr-19	Partially Submitted
Apr-19	Partially Submitted

Here I have two statuses (i.e. Submitted and Partially submitted). If the corresponding date has only Submitted status then I want to display it has 'Fully Submitted' for that date. If it has atleast one 'Partially Submitted' then i want to display 'In Progress' for that Month.

I have build a logic, which is only working for June and May but not for April.

Desired Output:

Date	Status1
Jun-19	In Progress
May-19	Fully Submitted
Apr-19	In Progress

Can anyone please help me with this?

Please find the attached QVW and source file. 

Thanks in advance!

Cheers,

Varun

[dplr-rn · Accepted solution]

change your load script to use below instead of the count

if( index(Concat(Status),'Partially Submitted')>0,'In Progress','Completed' ) as Status1