Update a column based on a condition

[paulista]

i have 4 columns.

EVT_TYPE	ID	COINED_UNCOINED	TIMESTAMP
-started-CPVT-EBS-COINED	1	coined	08/11/2019 12:53:48
-reviewCaseClaimed-CPVT	1		08/11/2019 12:54:10
-started-CPVT-ROI-UNCOINED	2	uncoined	11/11/2019 14:13:06
-ApproveDataOverride-APPROVED	2		11/11/2019 14:13:31

status can be coined or uncoined for an id.

so for each id i need the status filled. so if id is coined at min time then it should be coined always.

so expected output becomes

EVT_TYPE	ID	COINED_UNCOINED	TIMESTAMP
-started-CPVT-EBS-COINED	1	coined	08/11/2019 12:53:48
-reviewCaseClaimed-CPVT	1	coined	08/11/2019 12:54:10
-started-CPVT-ROI-UNCOINED	2	uncoined	11/11/2019 14:13:06
-ApproveDataOverride-APPROVED	2	uncoined	11/11/2019 14:13:31

this should work for script.

thanks 

[sunny_talwar]

Couple of questions before we can really answer it

1) Do you need this to be done in the script?

2) Can you have an ID repeat more than 2 times? If it can, what happens if it was coined on one occasion and uncoined in another.. do we change uncoined to coined if coined was associated with the min date?

[paulista]

@sunny_talwar 

yes need it to be done with a script , or expression if its simpler 

but an id will always have only coined or uncoined because it can be either of the two . no id can have multiple statuses. thats already mapped with a table.

[sunny_talwar · Accepted solution]

You can try this

SET TimestampFormat='MM/DD/YYYY hh:mm:ss';

Table:
LOAD * INLINE [
    EVT_TYPE, ID, COINED_UNCOINED, TIMESTAMP
    -started-CPVT-EBS-COINED, 1, coined, 08/11/2019 12:53:48
    -reviewCaseClaimed-CPVT, 1,  , 08/11/2019 12:54:10
    -started-CPVT-ROI-UNCOINED, 2, uncoined, 11/11/2019 14:13:06
    -ApproveDataOverride-APPROVED, 2,  , 11/11/2019 14:13:31
];

FinalTable:
NoConcatenate
LOAD EVT_TYPE,
	 ID,
	 If(ID = Previous(ID), If(Len(Trim(COINED_UNCOINED)) = 0, Peek('COINED_UNCOINED'), COINED_UNCOINED), COINED_UNCOINED) as COINED_UNCOINED,
	 TIMESTAMP
Resident Table
Order By ID, TIMESTAMP;

DROP Table Table;

[paulista]

@sunny_talwar thanks for the reply,

but now its  creating a new row with empty status for each event.

see below.

[sunny_talwar]

Can you share a qvw sample to show this?

[paulista]

@sunny_talwar . ATTACHED demo

[sunny_talwar]

It is difficult to say, but it might be a product of the multiple Joins that you are doing or the way you data is structured... I don't really know how your underlying data looks like... but it might be possible that you 

The part that I provided should work.

[paulista]

Thanks @sunny_talwar  that works made a change to data model