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

[pranaview]

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

[pradosh_thakur]

Please have a look at below.

You can modify the script to your requirement.

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

[pranaview]

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.

[pradosh_thakur]

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

[HirisH_V7]

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.

[HirisH_V7]

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.

[pranaview · Accepted solution]

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!

[pradosh_thakur]

Please close the thread. 🙂