topic Re: How do I update my existing data using incremental load? in QlikView

How do I update my existing data using incremental load?

kristeljoymalapitan — Sat, 16 Nov 2024 02:32:18 GMT

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

Re: How do I update my existing data using incremental load?

StarinieriG — Mon, 09 Sep 2019 08:35:51 GMT

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

Re: How do I update my existing data using incremental load?

kristeljoymalapitan — Mon, 09 Sep 2019 09:04:33 GMT

Thanks for your response StarinieriG,

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

Re: How do I update my existing data using incremental load?

StarinieriG — Mon, 09 Sep 2019 09:06:58 GMT

Hi,

Then:

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

Re: How do I update my existing data using incremental load?

kristeljoymalapitan — Mon, 09 Sep 2019 10:05:54 GMT

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

Re: How do I update my existing data using incremental load?

StarinieriG — Mon, 09 Sep 2019 10:09:51 GMT

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,

Re: How do I update my existing data using incremental load?

Brett_Bleess — Thu, 19 Sep 2019 17:01:29 GMT

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

Regards,
Brett

Re: How do I update my existing data using incremental load?

deepakahirwar — Fri, 20 Sep 2019 13:18:33 GMT

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