topic Re: Is it possible to implement incremental load(Insert+Update) without modified date in the table in QlikView

Is it possible to implement incremental load(Insert+Update) without modified date in the table

pranaview — Sat, 16 Nov 2024 21:10:23 GMT

Hi Guys,

I'm fairly new to the concept of incremental load in qlik and I understand that we require the modified date info to
load only the new records or update an existing one.

But in my source table, we are not capturing the modified date and I was wondering if i could implement it on the basis of Order_Number.

For ex: Existing records

Order_Number ModeofTransport
234 Truck
567 Sea
890 Truck

then when I load the same file again which has new/modified data
Order_Number ModeofTransport
234 Airfreight
997 Truck
1002 Sea

Final result
Order_Number ModeofTransport
234 Airfreight - Updated
567 Sea - Old
890 Truck - Old
997 Truck - New
1002 Sea - New

So as you can see, based on the order number, I want to implement the incremental load.

Please let me know if there's any way to achieve this as i need to get this done soon.

Thanks!

Pranav

Re: Is it possible to implement incremental load(Insert+Update) without modified date in the table

pradosh_thakur — Wed, 27 Mar 2019 07:29:42 GMT

Please have a look at below.

You can modify the script to your requirement.

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: Is it possible to implement incremental load(Insert+Update) without modified date in the table

pranaview — Wed, 27 Mar 2019 09:54:06 GMT

Hi Prodosh,
Thanks for the link.
Here they have used both the Modified_Date and the ID columns but i don't have Modified_Date in my source table.

Re: Is it possible to implement incremental load(Insert+Update) without modified date in the table

pradosh_thakur — Wed, 27 Mar 2019 10:05:54 GMT

Hi @pranaview

I have used it using the modified date always. There might be some way to do that but that would not be optimized as it would involve comparing each row . Below is the Qlik help link and it too contains a field called modified time.

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/LoadData/use-QVD-files-incremental-load.htm#anchor-4

Re: Is it possible to implement incremental load(Insert+Update) without modified date in the table

HirisH_V7 — Wed, 27 Mar 2019 10:12:28 GMT

In this case we need to compare the combo of occurrence, where ID will be unique.

Check out this below code, with only update and delete scenario into this.

Inc:
LOAD Order_Number&'|' & ModeofTransport as ID
FROM Incr.xlsx (ooxml, embedded labels, table is Sheet1);

LOAD ID
FROM Inc.qvd (qvd)Where not Exists (ID);

Inner Join 

LOAD Order_Number&'|' & ModeofTransport as ID
FROM Incr.xlsx (ooxml, embedded labels, table is Sheet1);

Store Inc into Inc.qvd(qvd);

Drop table Inc;

Main_Load:
Directory;
LOAD Subfield(ID,'|',1) as Order_Number,
Subfield(ID,'|',2) as ModeofTransport
FROM Inc.qvd (qvd);

PFA QVW for ref.

Re: Is it possible to implement incremental load(Insert+Update) without modified date in the table

HirisH_V7 — Wed, 27 Mar 2019 10:23:02 GMT

i suggest you to pick the modified time of any record in the table, so that it will be way easy to trace and load only those recently Updated. Beside loading the complete data, as incremental load meant that only.

Re: Is it possible to implement incremental load(Insert+Update) without modified date in the table

pranaview — Thu, 28 Mar 2019 06:49:55 GMT

Yeah, I couldn't really work the update functionality without a modified date. So, created a macro in the file itself to add Today's date in the file name when you save it.I'll pick that date from the file name while loading data into qlik and will use it as the modified date and compare with the existing data for updating the records if there's a match of Order_Number.

Thanks for the suggestions!

Re: Is it possible to implement incremental load(Insert+Update) without modified date in the table

pradosh_thakur — Thu, 28 Mar 2019 07:01:34 GMT

Please close the thread. 🙂