7 Replies Latest reply: May 29, 2015 3:59 AM by Toon Van den Eynde RSS

    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

        • Re: Load data with where condition
          Friedrich Hofmann

          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
            Alessandro Saccone

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

             

            LOAD *

            WHERE timestamp = MAX(timestamp)

            SQL SELECT *

            FROM dwh

              • 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
                    Alessandro Saccone

                    Try with:

                     

                    MyTab:

                    SELECT MAX(timestamp) as maxi FROM dwh;

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

                     

                    LOAD *

                    WHERE timestamp = $(myMax);

                    SQL SELECT *

                    FROM dwh

                      • 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