topic Re: Update a column based on a condition in QlikView

Update a column based on a condition

paulista — Sat, 16 Nov 2024 19:27:02 GMT

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

Re: Update a column based on a condition

sunny_talwar — Mon, 16 Dec 2019 13:40:57 GMT

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?

Re: Update a column based on a condition

paulista — Mon, 16 Dec 2019 13:46:25 GMT

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

Re: Update a column based on a condition

sunny_talwar — Mon, 16 Dec 2019 13:54:05 GMT

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;

Re: Update a column based on a condition

paulista — Mon, 16 Dec 2019 14:49:39 GMT

@sunny_talwar thanks for the reply,

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

see below.

Re: Update a column based on a condition

sunny_talwar — Mon, 16 Dec 2019 14:59:37 GMT

Can you share a qvw sample to show this?

Re: Update a column based on a condition

paulista — Mon, 16 Dec 2019 18:11:42 GMT

@sunny_talwar . ATTACHED demo

Re: Update a column based on a condition

sunny_talwar — Mon, 16 Dec 2019 15:17:22 GMT

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.

Re: Update a column based on a condition

paulista — Mon, 16 Dec 2019 18:12:27 GMT

Thanks @sunny_talwar that works made a change to data model