Incremental only update..

[Tool_Tip]

Dear all,

I have table such as below  without any modification date fields and every day the period is changes based on backend calculations.

Day:1

ID        Period

1         2Yrs-3months-10days

2         4Yrs-5Months-0days

3         0Yrs-1Month-10days

Day:2

ID        Period

1         2Yrs-3months-10days

2         7Yrs-2Months-0days

3         0Yrs-1Month-11days

Our requirement is display the latest Period for each ID. How can we achieve this.

[marksouzacosta]

Hi @Tool_Tip,

A few questions:

1. How many records do you have in your data source? 
2. Do you need to keep historical chances?
3. Can a Period be reduced instead of increased?

Regards,

Mark Costa

[Tool_Tip]

Hi Mark,

Below answeres:

1. How many records do you have in your data source?  Almost 300000
2. Do you need to keep historical chances?  No need, only latest change to display
3. Can a Period be reduced instead of increased? Either..

[marksouzacosta]

Hmm maybe in your scenario it is better to just do a full load every time instead of an incremental load. How much time to do a full load?

[Tool_Tip]

We have aborted after keep it loading on 24hrs... 😉 so not sure how much total time it will take to completed load.

[marksouzacosta]

Awesome. I would need more details to be able to help you. Like, what is the data source, what are the columns, sample values (if possible), your current code (if possible). Qlik offer many possible solutions for ETLs. I believe a mix of Data Partition, Incremental Load and some progressive saving QVDs may be a solution for you.

[ali_hijazi]

Hello
You are saying you got 300,000 records
that's a sip of water for Qlik so no need for incremental reload. 
the historical data / changes you are talking about are in the data source or in your qvd file?
we need more details about connetion, script, ... in order to resolve the issue

[Tool_Tip]

Its 3000000. My bad.

Obviously data changes in source. 

[ali_hijazi]

still 3M is handled easily by Qlik
and here too there is no need for incremental reload
so the question is are you experiencing low performance during loading the data or on the user interface?
if you are experiencing low performance on the user interface then you need to review the schema, otherwise it is the database that is slow in responding; in this case sometmes we change the connector that we are using to connect to the database;
for example we had an oracle database to connect to, and we found out that the OLEDB connection is faster than using the Oracle connector available in Qlik:

last thing your SQL statement is it a simple select * from table or you are making join and other transformations against the data source?

[marcus_sommer]

Like already hinted it's not a big data-set if it's directly loaded from a table. Therefore I assume it's the result of a (complex) query against a much bigger data-set within the data-base - and here is the data-base itself the bottleneck respectively the belonging query (probably with a lot of sub-queries which might be replaced with tables/views and/or may extended to appropriate flags which record is the first/last one).

You may also considering to transfer the complete logic into Qlik by loading just the raw-data from the data-base incrementally and applying the needed transformations on top of it in n incremental layers.