How do I update my existing data using incremental load?

[kristeljoymalapitan]

Hi, I have below data, I need to update the status of the account manually every month, please see below scenario

Scenario: to update/replace ACCT 1 of JUN-2019 monthend to CLOSED status, only Status will be replace other details will be the still. (Below data are on existing QVD file).

I have an excel file that contains updated records of ACCT 1 of JUN-2019 monthend with the same number of columns

ACCOUNT NUMBER	STATUS	NAME	MONTHEND
ACCT 1	ACTIVE	KRISTEL	APR-2019
ACCT 1	ACTIVE	KRISTEL	MAY-2019
ACCT 1	ACTIVE	KRISTEL	JUN-2019
ACCT 2	ACTIVE	JUL-2019	JUL-2019
ACCT 3	CLOSED	AUG-2019	AUG-2019

expected output will be the ff:

ACCOUNT NUMBER	STATUS	NAME	MONTHEND
ACCT 1	ACTIVE	KRISTEL	APR-2019
ACCT 1	ACTIVE	KRISTEL	MAY-2019
ACCT 1	CLOSED	KRISTEL	JUN-2019
ACCT 2	ACTIVE	JUL-2019	JUL-2019
ACCT 3	CLOSED	AUG-2019	AUG-2019

Thank  you

[StarinieriG]

Hi,

you could try in this way:

1) Add a variable 

LET Closed = 'JUN-2019';

2) Then create a new field

If(Match(MONTHEND,'$(Closed)')>0,'CLOSED',STATUS) AS STATUS_NEW

[kristeljoymalapitan]

Thanks for your response StarinieriG,

I don't want to add new column, just replace/update the status from CLOSED.

[StarinieriG]

Hi,

Then:

LOAD
[ACCOUNT NUMBER],
If(Match(MONTHEND,'$(Closed)')>0,'CLOSED',STATUS) AS STATUS,
NAME,
MONTHEND;

[kristeljoymalapitan]

How about the other status that not need to replace under Jun-2019 monthend (for example, there is TERMINATED status under JUN-2019 monthend but will not be replace or update)

ACCOUNT NUMBER	STATUS	NAME	MONTHEND
ACCT 1	ACTIVE	KRISTEL	APR-2019
ACCT 1	ACTIVE	KRISTEL	MAY-2019
ACCT 1	ACTIVE	KRISTEL	JUN-2019
ACCT 2	TERMINATED	KRISTEL	JUN-2019
ACCT 2	ACTIVE	JUL-2019	JUL-2019
ACCT 3	CLOSED	AUG-2019	AUG-2019

[StarinieriG]

Hi

in that case, you have to add a condition, if you want to update only active status:

If(Match(MONTHEND,'$(Closed)')>0 and STATUS = 'ACTIVE','CLOSED',STATUS) AS STATUS,

[Brett_Bleess]

Kristel, did any of the posts help you get what you needed?  If so, be sure to use the Accept as Solution button on those that did help to give the poster credit for the help as well as to let others know what worked.  I am going to put a couple of links below that may help further if you are still working on things, but if you still need further help, please leave an update too.

https://community.qlik.com/t5/Qlik-Design-Blog/Overview-of-Qlik-Incremental-Loading/ba-p/1466780

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/QVD_Incremental...

Regards,
Brett

[deepakahirwar]

Please refer the link below  to overcome this problem:

https://ezdatamunch.com/implementation-incremental-load-qlikview-benefits/

Note: Go through the 2.Case Insert & Update

Thanks & Regards,

Deepak Ahirwar