I use 15 tables for a qliksense dashboard and I have loaded all these tables until yesterday into a Single QVD called MASTER_QVD.
Now I created a MAXDate field in another query based on MASTER QVD.
I passed this into a parameter using LET.
Then I imported all the 15 tables from HIVE with WHERE dt_Key>'$(MAXDate)';
which fetched 2000 records for 1 day for table1.which is correct. however only this last step of bringing 2000 records tool almost 30 mins. for first table alone. all the tables together kep running past 1Hr 45 Mins.
Please help. how can I make this run faster? Thanks a lot!
My Script:
Tables_All15:
Load *
SQL SELECT *
From Hive.Schema.Table1name;
Concatenate
Load *
SQL SELECT *
From Hive.Schema.Table2Name;
so on.. until 15 tables
STORE Tables_All15 INTO[lib://storagepath/MASTER.QVD] (qvd);
TEMP:
Load COUNTRY,
Max(dt_key) as Maxdate
Resident MASTER;
MaxDate_table: Load COUNTRY, Max(dt_key) as Maxdate Resident TEMP GROUP BY COUNTRY;
let vMaxDate=Date(Peek('Maxdate',0,'MaxDate_table')); TRACE vMaxDate=$(vMaxDate);
Drop Table TEMP; Drop table MaxDate_table;
NEW_RECORDS:
LOAD *
where date(dt_key,'dd/mm/yyyy')>'$(vMaxDate)';
SQL SELECT *
FROM Hive.Schema.Table1;
concatenate
Load *
where date(dt_key,'dd/mm/yyyy')>'$(vMaxDate)';
SQL SELECT *
FROM Hive.Schema.Table12; ... so on...
and then I am concatenating it with MASTER QVD and saving it back to Master QVD.
* If you've already pulled all the information into a QVD, why are you loading it again from the data source?
* It looks like there's a missing GROUP BY for the TEMP load?
* Why are you grouping by Country if you're only going to read the value once anyway? Why not just get the global max?
* You don't seem to be dropping Tables_All15, and you aren't doing NOCONCATENATE LOAD for MaxDate_table even though it has the exact same fields as TEMP?
* You don't seem to actually load from MASTER.QVD except to get the max date, so this doesn't seem to be incremental to begin with?
* You aren't passing the max date to the underlying query, meaning it still takes the full time to read the data before it can be filtered by Qlik.
Based on your description, it sounds like something is going wrong with the concatenation, not the condition. I'd also suggest running in debug mode to make sure the variable is getting populated / read correctly.
* If you've already pulled all the information into a QVD, why are you loading it again from the data source? - I have loaded all my exisitng data until yesterday into Master QVD. Now I am loading today's data ONLY from HIVE and again load it QVD at the end. This runs every day and picks only latest records from Hive.
* It looks like there's a missing GROUP BY for the TEMP load? - We can Group by only with Aggregation, TEMP does not have any Aggregation hence no need to do it there.
* Why are you grouping by Country if you're only going to read the value once anyway? Why not just get the global max? - I have 15 markets data in QVD and each market will have different max date as some will have -2 days instead of -1.
* You aren't passing the max date to the underlying query, meaning it still takes the full time to read the data before it can be filtered by Qlik. - I am passing it and using it in NEW_RECORDS table.
* You don't seem to actually load from MASTER.QVD except to get the max date, so this doesn't seem to be incremental to begin with? - I am concatinating the MAster QVD with latest records table in th end. I mentined in my explaination
* You don't seem to be dropping Tables_All15, and you aren't doing NOCONCATENATE LOAD for MaxDate_table even though it has the exact same fields as TEMP? - I am dropping it but I missd it add it here
I did use it in the SQI SELECT initially, but it is fetching 0 records. so I had to use it in load. it is taking too long but fetching records.
Also date format I am using for MAxdate is 'DD/MM/YYYY' as you can see. but my dt_key is in 'mm-dd-yyyy' so I have to format it in load. in SQL it is not wrking. Please help
" I have 15 markets data in QVD and each market will have different max date as some will have -2 days instead of -1."
I don't see anything in your code that loops through the countries. You're only pulling the date once at the start.
I am passing it and using it in NEW_RECORDS table.
You seem to be using it in the Qlik load WHERE statement. Doing so is inefficient - it means you need to load the entire data from SQL before it gets filtered by Qlik. I would recommend adding it to the query's WHERE condition instead (or to both, if you want).
If you still have the issue after adding the condition to your SQL WHERE, I would again suggest running in debug mode to check the population of the variable, and otherwise, try running the script without concatenating the tables to see if that's where the problem stems from.
