Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Load data with where condition

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

7 Replies
datanibbler
Esteemed Contributor

Re: Load data with where condition

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

Re: Load data with where condition

But you compute MAX(timestamp) in another query or the following is exactly your code?

LOAD *

WHERE timestamp = MAX(timestamp)

SQL SELECT *

FROM dwh

Not applicable

Re: Load data with where condition

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!

Re: Load data with where condition

Try with:

MyTab:

SELECT MAX(timestamp) as maxi FROM dwh;

LET myMax = Peek('maxi',0,'MyTab');

LOAD *

WHERE timestamp = $(myMax);

SQL SELECT *

FROM dwh

Not applicable

Re: Load data with where condition

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

fredericmante
New Contributor III

Re: Load data with where condition

try this code

MyTab:

SQL SELECT MAX("LAST_UPDATE") as maxi FROM DWH."FACT_BUSINESS_FIGURES";

LET myMax = Peek('maxi',0,'MyTab);

regards

Not applicable

Re: Load data with where condition

Pardon me, I adjusted this already and keep getting the same error.

But thanks for the remark.

Community Browser