Create a Flag/Indicator at the start of a value occurring in a field

[fishing_weights]

Thanks in advance

I've a table:

ID	Date	Status
1	1jan23	good
1	2jan23	bad
1	3jan23	bad
1	4jan23	good
1	5jan23	bad
1	6jan23	bad
2	1jan23	bad
2	2jan23	bad
2	3jan23	good
2	4jan23	good
2	5jan23	bad
2	6jan23	bad

I'm try to create a flag where for each ID, when the status changes from good to bad

so the end result should be the new field: Flag

ID	Date	Status	Flag
1	1jan23	good
1	2jan23	bad	1
1	3jan23	bad
1	4jan23	good
1	5jan23	bad	1
1	6jan23	bad
2	1jan23	bad	1
2	2jan23	bad
2	3jan23	good
2	4jan23	good
2	5jan23	bad	1
2	6jan23	bad

I've tried but it does not work...

Load *,

If(ID=peek(ID) and Status<>peek(status) and Status='bad',1,'') as Flag

Resident[Original_Table]

order by ID,Date desc;

[Mark_Little]

Hi 

I would try ordering the original table and then try the same script without the order by. 

I would probably change the middle argument to Peek(Status) = 'good'

[fishing_weights]

thanks for the suggestion but it does not work. nothing is getting 'flagged'

[MarcoWedel]

another possible solution might be 

table1:
LOAD ID,
     Date#(Date,'DDMMMYY') as Date,
     Status
Inline [
ID	Date	Status
1	2jan23	bad
2	3jan23	good
2	1jan23	bad
1	1jan23	good
2	5jan23	bad
1	3jan23	bad
1	4jan23	good
2	4jan23	good
1	6jan23	bad
1	5jan23	bad
2	2jan23	bad
2	6jan23	bad
] (delimiter is '\t');


table2:
LOAD *,
     If(Status='bad' and (ID<>Previous(ID) or Previous(Status)='good'),1) as Flag
Resident table1
Order By ID, Date;

DROP Table table1; 

hope this helps

Marco

[fishing_weights]

Hi @MarcoWedel  Thank you for this it works. I had to put it as a mapping table as i realise there could be other fields affecting. so something like this:

[MAP]:
Mapping LOAD ID&Date&Status as key, 1 as ind
Resident[table1]
Where Status ='bad' and (ID<>Previous(ID) or Previous(Status )='good')
Order by ID,Date asc;

and then 

[table2]:

load *,

applymap('MAP',ID&Date&Status,0) as Flag

resident [table1];

However now ID who "appear" mid way are not able to be flagged out. Example if I have ID 3 and it starts on 3Jan23 immediately as "bad".

table1:
LOAD ID,
     Date#(Date,'DDMMMYY') as Date,
     Status
Inline [
ID	Date	Status
1	2jan23	bad
2	3jan23	good
2	1jan23	bad
1	1jan23	good
2	5jan23	bad
1	3jan23	bad
1	4jan23	good
2	4jan23	good
1	6jan23	bad
1	5jan23	bad
2	2jan23	bad
2	6jan23	bad
3       3Jan23  bad
3       4Jan23  bad
3       5Jan23  bad
3       6Jan23  bad
] (delimiter is '\t');

[MarcoWedel · Accepted solution]

Hi,

using your example I got this result:

table1:
LOAD ID,
     Date#(Date,'DDMMMYY') as Date,
     Status
Inline [
ID	Date	Status
1	2jan23	bad
2	3jan23	good
2	1jan23	bad
1	1jan23	good
2	5jan23	bad
1	3jan23	bad
1	4jan23	good
2	4jan23	good
1	6jan23	bad
1	5jan23	bad
2	2jan23	bad
2	6jan23	bad
3	3Jan23	bad
3	4Jan23	bad
3	5Jan23	bad
3	6Jan23	bad
] (delimiter is '\t');

[MAP]:
Mapping LOAD ID&Date&Status as key, 1 as ind
Resident [table1]
Where Status ='bad' and (ID<>Previous(ID) or Previous(Status )='good')
Order by ID,Date asc;

[table2]:
load *,
applymap('MAP',ID&Date&Status,0) as Flag
resident [table1];

DROP Table table1; 

Can you explain how this is different from what you expected?

thanks

Marco

[fishing_weights]

Thanks! @MarcoWedel yes it works. I realized the mistake I made was was filtering the table after I mapped further down the script which caused some of the flag as 1 to "disappear".

Wasted a few days! trying to find work arounds and thinking these is some bug with the conditional statement