Count the Occurrence of a field value excluding the 1st occurrence in the script

[Qliklearner3]

Hi Guys,

I've to count the number of occurrences when the status was submit excluding the 1st one i.e starting from 2nd occurrence wherever the status is submit I need to count that (gives a count of 1 )and also add a new column that states the rework Type i.e. we need to check the previous value wherever the status is submit and define if the previous value was rejected then state reject else if there is any other reason like pending state external. 

Table :

ID,status,Date

1,Created,10-2-2019

1,Queued,11-2-2019

1,Progress,14-2-2019

1,Submit,18-3-2019

1,Rejected,19-3-2019

1,Submit,20-3-2019

1,Rejected,23-3-2019

1,Pending,23-3-2019

1,Submit,24-3-2019

1,Approved,24-3-2019

Output :

ID,status,Date,Occurrence,Rework Type

1,Created,10-2-2019, 0,-

1,Queued,11-2-2019, 0,-

1,Progress,14-2-2019, 0,-

1,Submit,18-3-2019, 0,- (ignore the first occurrence)

1,Rejected,19-3-2019, 0,Reject

1,Submit,20-3-2019, 1,-

1,Rejected,23-3-2019,0,-

1,Pending,23-3-2019, 0, External,

1,Submit,24-3-2019,1,-

1,Approved,24-3-2019,0,-

@swuehl @sunny_talwar please help.

Thanks

[Kushal_Chawda]

@Qliklearner3  you have below duplicate? 

1,Rejected,19-3-2019

[Qliklearner3]

Hi @Kushal_Chawda  my bad I've corrected it there aren't duplicates , do you have any solution for this ?

[Kushal_Chawda]

@Qliklearner3  why not Rework Type ='Reject' for below line because it's previous line is Submitted?

1,Submit,20-3-2019, 1,-

1,Rejected,23-3-2019,0,-

[Qliklearner3]

@Kushal_Chawda we'll check wherever the status is submit we need to check the record before and read its status in this case before submit the status was pending his external.

But my first question is I've to count the number of occurrences when the status was submit excluding the 1st one i.e starting from 2nd occurrence wherever the status is submit I need to count that can you help me with this ?

[Kushal_Chawda]

@Qliklearner3  try below

Data:
LOAD 
    pick(match(lower(trim(status)),'rejected','pending'),'Reject','External') 
    as status_flag,
    ID,
    status,
    Date
FROM Table;

Left Join(Data)
LOAD ID,
     min(Date) as Date ,
     0 as Rework_Type1
Resident Data
where lower(trim(status))='submit'
Group by ID;

Final:
LOAD *,
     if(len(trim(Rework_Type1))=0 ,if( lower(trim(status))='submit',1,
     if(len(trim(Rework_Type2))>0,Rework_Type2,0)),0) as Rework_Type;
LOAD *,

     if(ID=Previous(ID) and len(trim(status_flag))>0 and lower(trim(previous(status)))='submit' and len(trim(Previous(Rework_Type1)))=0,status_flag) as Rework_Type2
Resident Data
Order by ID,Date desc, status asc;

DROP Table Data;

DROP Fields Rework_Type1,Rework_Type2,status_flag;

Note:  Here correct order of data is most important as you are using previous functions. Check order by statement carefully and remove ,status asc if not needed.