Incremental Load with multiple table joins

[John2]

Good Morning All. We are having some challenges to process the incremental load. I guess, for optimization reasons, Qlik is splitting our json files into multiple tables after connecting to MongoDB. We are trying to merge the two tables via a join and load the data.

initial load

[car-data]:
LOAD
 [_id],
    [car_model],
    [car_year],
    [car_price]
 FROM [lib://Qlik/qlik-car-data.xlsx]
(ooxml, embedded labels, table is car);

Left Join
LOAD
 [_id],
    [car_company],
    [car_company_owner],
    [seq_number]
 FROM [lib://Qlik/qlik-car-data.xlsx]
(ooxml, embedded labels, table is meta);

Store [car-data] into [Lib://test-conn/car-data.QVD] (qvd);

Drop Table [car-data];

it works fine.

We are finding the max seq # (which is present in meta file)

Temp_MaxSeqNbr:
load max([seq_number]) as MaxSeqNbr
FROM [Lib://test-conn/car-data.QVD] (qvd);

Let vMaxSeqNbr = Peek('MaxSeqNbr', 0, 'Temp_MaxSeqNbr');

Trace $(vMaxSeqNbr);

Let MyMessage = NoOfRows('Temp_MaxSeqNbr') & ' rows in Temp_MaxSeqNbr Table';

Trace $(MyMessage);

Drop Table Temp_MaxSeqNbr;

incremental load

[car-incr]:
LOAD
 [_id],
    [car_model],
    [car_year],
    [car_price]
 FROM [lib://Qlik/qlik-car-data1.xlsx]
(ooxml, embedded labels, table is car);

Left Join
LOAD
 [_id],
    [car_company],
    [car_company_owner],
    [seq_number]
 FROM [lib://Qlik/qlik-car-data1.xlsx]
(ooxml, embedded labels, table is meta)
where [seq_number] > $(vMaxSeqNbr);

Concatenate LOAD
 [_id],
    [car_model],
    [car_year],
    [car_price],
    [car_company],
    [car_company_owner],
    [seq_number]
FROM [Lib://test-conn/car-data.QVD] (qvd)
WHERE NOT EXISTS([_id]);

Inner Join
Load
 [_id]
FROM [lib://Qlik/qlik-car-data1.xlsx]
(ooxml, embedded labels, table is meta);

Store [car-incr] into [Lib://test-conn/car-data.QVD] (qvd);

Exit Script;

We can see all data - but meta data is missing for the initially loaded records.

Our plan is to identify all the records that are added (from two tables via a join) and apply updates and apply deletes.

Can anyone let us know what is the issue with the above approach.

Thank you for the help.

[rubenmarin]

Hi, I think the 'Left Join' should be a 'Right Join' or an 'Inner Join' so you start with new meta and add the id's to only have new ids loaded.

The script as it is now first loads all data from cars, then add the meta from the new, but the old (with all his id's) are still loaded just without meta. When you do the contanate the id's are already loaded and the not exists prevent them to load.

[John2]

Thank you rubenmarin for your response.

I started with meta and did a right join on cars. But I am still having the same issue.

the issue is identifying the delta from two tables.

[car-delta]:
LOAD
 [_id],
    [car_company],
    [car_company_owner],
    [seq_number]
 FROM [lib://Qlik/qlik-car-data1.xlsx]
(ooxml, embedded labels, table is meta)
where [seq_number] > $(vMaxSeqNbr);

Right Join
LOAD
 [_id],
    [car_model],
    [car_year],
    [car_price]
 FROM [lib://Qlik/qlik-car-data1.xlsx]
(ooxml, embedded labels, table is car);

Store [car-delta] into [Lib://test-conn/car-delta.QVD] (qvd);

Exit Script;

it is identifying the delta from meta, but not the cars. and while inserting into the QVD it is writing all records without meta info. do you have any suggestions for processing incremental loads of multiple tables into one qvd. I haven't found any examples.

[John2]

after changing it to inner join, I can see the delta record. but because of the "WHERE NOT EXISTS([_id])" in the concatenate block, I am not getting the correct info as the result. please see the attached screenshot. after removing the "where not exists" it is working for insert and delete.

[rubenmarin]

If you load the meta first you should keep the left join, switch Left for Right and the order of tables to load ends with the same login:

A left join B = B right Join A.

Qlik help has some examples on how to do incremental loads: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/QVD_Incremental...