Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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?

Highlighted
Contributor III
Contributor III

@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.

Highlighted

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

Highlighted
Contributor III
Contributor III

@sunny_talwar thanks for the reply,

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

see below.

coined uncoined.PNG

Highlighted

Can you share a qvw sample to show this?

Highlighted
Contributor III
Contributor III

@sunny_talwar . ATTACHED demo

Highlighted

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.

Highlighted
Contributor III
Contributor III

Thanks @sunny_talwar  that works made a change to data model