Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a database that functions as a data warehouse. One of the fields it keeps is a timestamp, with the last time data was loaded into the database.
Based on this timestamp, I want to load the data only with the most current timestamp into my QlikSense application.
This is the statement I use to load the data.
LOAD *
WHERE timestamp = MAX(timestamp)
SQL SELECT *
FROM dwh
When I don't use the Where-clause, the data takes about 1 minute to load (almost 1 million records).
When I use the Where-clause however, it takes longer than 40 minutes (still counting as we speak). Is there a reason why it suddenly takes so long? Am I using a wrong statement? Thanks in advance!
Kind Regards,
Toon
Hi Toon,
I can only guess here: Since you have tried it and without the WHERE clause, it is fast, I assume that it's not the SQL part causing problems.
What does the timestamp consist of? Is it the usual >> DD.MM.YYYY hh:mm:ss <<?
In that case, that is a so-called high_cardinality field as it has very many different values. Is new data loaded at least once a day? Then you could re-format it and drop the day and just keep the time_part, that might make it faster.
Generally, disintegrating it into a date and a time might make it faster as then there will be significantly fewer different values of that field. That is generally recommended in loading data.
HTH
But you compute MAX(timestamp) in another query or the following is exactly your code?
LOAD *
WHERE timestamp = MAX(timestamp)
SQL SELECT *
FROM dwh
It's is my exact query. But shouldn't it work this way?
Or do I need to use a subquery like this:
LOAD *
WHERE timestamp = (SQL SELECT MAX(timestamp) FROM dwh);
SQL SELECT *
FROM dwh
thanks in advance!
Try with:
MyTab:
SELECT MAX(timestamp) as maxi FROM dwh;
LET myMax = Peek('maxi',0,'MyTab');
LOAD *
WHERE timestamp = $(myMax);
SQL SELECT *
FROM dwh
I keep getting the "FIELD NOT FOUND - <=>" error. The error occurs in the bold section (below).
Any ideas why? Because I have no idea.
Above I was using example code, this is the real statement I'm using.
MyTab:
SQL SELECT MAX("LAST_UPDATE") as maxi FROM DWH."FACT_BUSINESS_FIGURES";
LET myMax = Peek('newest',0,'MyTab);
Fact_Business_Figures:
LOAD "GROSS_UNIT_RATE",
"GROSS_RATE",
"GROSS_MEDIA",
"GROSS_PAYING",
"TECH_PROD_COSTS",
"POST_DISTR_CHARGES",
TAXES,
"INSERTION_DT_ID",
"INSERTION_ID",
"COMPANY_ID",
"VPD_COMPANY_ID",
"AGREEMENT_ID",
"CAMPAIGN_ID",
"BRAND_ID",
"PRODUCT_ID",
"MEDIUM_ID",
"ESTIMATE_ID",
"SALES_ACG",
"ESTIMATE_BEGIN_DT_ID",
"ESTIMATE_END_DT_ID",
"LAST_UPDATE"
WHERE "LAST_UPDATE" = $(myMax);
SQL SELECT "GROSS_UNIT_RATE",
"GROSS_RATE",
"GROSS_MEDIA",
"GROSS_PAYING",
"TECH_PROD_COSTS",
"POST_DISTR_CHARGES",
"TAXES",
"INSERTION_DT_ID",
"INSERTION_ID",
"COMPANY_ID",
"VPD_COMPANY_ID",
"AGREEMENT_ID",
"CAMPAIGN_ID",
"BRAND_ID",
"PRODUCT_ID",
"MEDIUM_ID",
"ESTIMATE_ID",
"SALES_ACG",
"ESTIMATE_BEGIN_DT_ID",
"ESTIMATE_END_DT_ID",
"LAST_UPDATE"
FROM DWH."FACT_BUSINESS_FIGURES"
Please help.
Kind regards,
Toon
try this code
MyTab:
SQL SELECT MAX("LAST_UPDATE") as maxi FROM DWH."FACT_BUSINESS_FIGURES";
LET myMax = Peek('maxi',0,'MyTab);
regards
Pardon me, I adjusted this already and keep getting the same error.
But thanks for the remark.