To give you some context, I have 16 tables that are essentially the same but correspond to different years. I need to filter by a specific record, such as an 'id'. Each table contains between 6 and 12 million records, so loading all the records and then filt
If the tables are the same (same columns with same column names) you should concatenate them so that they become just one table in the data model in Qlik. Then you will have no problems filtering on the different dimensions.
For what exactly do you need an ID in the rows? If it is for validation purposes, you could try the Autonumber() function on the fields that uniquely identify a table row.
If you are concatenating your tables, maybe you want to track where each record comes from. In that case, adding QvdName() as a data-source identifier could help.
For more specific help, could you clarify your goal in this situation?
Hi! It looks like your message was cut off at the end.. I understood that you have 16 year-based tables (same structure) and you want to optimize memory and performance — avoid loading all records across all years if you're only interested in a specific id.
If you’re not already using QVDs, this is the first big step. QVDs are Qlik’s optimized storage format.
You can create one QVD for each year (or already have them), and then use a where clause to selectively load only records with a specific id.
Example:
Let vMyID = '123456';
LOAD * FROM [lib://MyQVDs/Data_2010.qvd] (qvd) WHERE id = '$(vMyID)';
LOAD * FROM [lib://MyQVDs/Data_2011.qvd] (qvd) WHERE id = '$(vMyID)';
...
LOAD * FROM [lib://MyQVDs/Data_2024.qvd] (qvd) WHERE id = '$(vMyID)';
This way, only the rows matching the id are loaded, drastically reducing memory usage.
Instead of hardcoding every year, you can loop through the years:
Let vMyID = '123456';
FOR vYear = 2010 to 2024 LOAD * FROM [lib://MyQVDs/Data_$(vYear).qvd] (qvd) WHERE id = '$(vMyID)'; NEXT
You can even make the year range dynamic based on available files.
Things to Avoid! Don’t load all 16 full tables unless you absolutely need them and avoid joining the large tables unless optimized, especially without filters.
