4 Replies Latest reply: Aug 31, 2018 10:09 AM by Liv ma RSS

    Using variables in WHERE clause

    Liv ma

      Hello,

       

      I've read several other topics on the same issue but the solutions did not work for me so I kindly ask for your help.

       

      I have a sales table which I load with the following statement:

       

      sales_table:

      LOAD

      date,

      client_id,

      product_id,

      sales

      FROM .....;

       

      what I want is to create a separate table storing total sales per customer as of the latest date

       

      in order to do that, I first define a variable which calculates the maximum date:

       

      let vMaxDat = 'max(date)';

       

      sales_last_table:

      LOAD

      date,

      client_id,

      sum(sales)

      resident sales_table

      WHERE date = $(vMaxDat)

      group by date, client_id;

       

      I have tried Set instead of Let and any combination of commas and parenthesis in the WHERE clause, however I never get any value in the date field from the sales_last_table and therefore I get no sales

       

      the variable does store the correct value (I see it in a sheet) and if I use the actual date in WHERE clause, it works, but not with a variable

       

      however I need to use the variable in order to eliminate the need to manually change the date at each script run

       

      I should mention that the date in the original table is formatted as 'MMM-YY'

       

      thank you.

        • Re: Using variables in WHERE clause
          Gysbert Wassenaar

          let vMaxDat = 'max(date)'; doesn't calculate anything. It assigns the string 'max(date)' to the variable vMaxDat. That will not work in the WHERE clause. You first need to calculate the max date before you can use it:

          tmpMaxDate:

          LOAD max(Date) as MaxDate RESIDENT sales_table;


          LET vMaxDat = num(peek('MaxDate'));


          DROP TABLE tmpMaxDate;


          Now vMaxDat has a value you can use in the WHERE clause.


            • Re: Using variables in WHERE clause
              Liv ma

              thanks for the answer, unfortunately I did not get it to work: the script runs, but there is no value in the date field of the second table

               

              how exactly is the syntax for the WHERE clause using your temporary table?

               

              also, you say that let vMaxDat = 'max(date)' does not calculate anything, but if I put it in a KPI object I see the correct date

              I have a feeling that this has something to do with the way the date is formatted in the original table: it is formatted as MMM-YY via the script and I do not think that it is stored as a number...

            • Re: Using variables in WHERE clause
              Anil Samineni

              Instead, You can try this?

               

              Table:

              Load date, client_id, product_id,

              sales FROM .....;

              Right Join (Table)

              Load Max(date) as date Resident Table;