Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulista
Contributor III
Contributor III

Update a column based on a condition

i have 4 columns.

EVT_TYPEIDCOINED_UNCOINEDTIMESTAMP
-started-CPVT-EBS-COINED1coined08/11/2019 12:53:48
-reviewCaseClaimed-CPVT1 08/11/2019 12:54:10
-started-CPVT-ROI-UNCOINED2uncoined11/11/2019 14:13:06
-ApproveDataOverride-APPROVED2 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_TYPEIDCOINED_UNCOINEDTIMESTAMP
-started-CPVT-EBS-COINED1coined08/11/2019 12:53:48
-reviewCaseClaimed-CPVT1coined08/11/2019 12:54:10
-started-CPVT-ROI-UNCOINED2uncoined11/11/2019 14:13:06
-ApproveDataOverride-APPROVED2uncoined11/11/2019 14:13:31

 

 

this should work for script.

 

thanks 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

8 Replies
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
Contributor III
Contributor III
Author

@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

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

@sunny_talwar thanks for the reply,

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

see below.

coined uncoined.PNG

sunny_talwar

Can you share a qvw sample to show this?

paulista
Contributor III
Contributor III
Author

@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 

image.png

The part that I provided should work.

paulista
Contributor III
Contributor III
Author

Thanks @sunny_talwar  that works made a change to data model