CODEWITHSUNDEEP
arraysif-statementgoogle-sheetsarray-formulasgoogle-sheets-query
ftft32
ftft32

Reputation: 19

Google Spreadsheet Query IF(ISBLANK(), PARSE_ERROR:

With this Query I get this result. The idea is i have a date of the product that is sold. When check if the date is between date_start and date_end of the marketing table, if so put that price in the marketingprice.

In some cases there is no end. This mean it's still running and has no end date. We still use them. Because there is no end date I want to use the date of today. So if E (date_end) is empty , use the today date if not then use E

=query(Marketing!$B$2:E,
"select C,D  where  B='" & D2 & "' and 
D<=date '"&TEXT(E2,"yyyy-MM-dd")&"' and 
date'"&TEXT(today(),"yyyy-MM-dd")&"'>=date '"&TEXT(E2,"yyyy-MM-dd")&"' ")

+------------+---------------+--------+-----------------+----------+----------------+
| product_no | product_price | amount | deliver_country |  datum   | marketingprice |
+------------+---------------+--------+-----------------+----------+----------------+
|       1001 |           2.8 |      2 | de              | 2-1-2020 |                |
+------------+---------------+--------+-----------------+----------+----------------+
+-----+------------+
| 3.2 | 01-01-2020 |
| 1.2 | 02-01-2020 |
+-----+------------+

I want to use IF(isblank(E),date'"&TEXT(TODAY(),"yyyy-MM-dd")&"', E)

Then the code will be 

=query(Marketing!$B$2:E,
"select C,D  where  B='" & D2 & "' and 
D<=date '"&TEXT(E2,"yyyy-MM-dd")&"' and 
IF(isblank(E),date'"&TEXT(today(),"yyyy-MM-dd")&"',E)>=date '"&TEXT(E2,"yyyy-MM-dd")&"' ")

Then I get a error:

QUERY: PARSE_ERROR: Encountered " "IF "" at line 1, column 58. Was expecting one of: "(" ... "(" ...

Marketing Table

+---------+---------+-------+-------------+------------+
| channel | country | price | date_start  |  date_end  |
+---------+---------+-------+-------------+------------+
| Google  | de      |   3.2 | 01-01-2020  | 01-01-2020 |
| Google  | de      |   1.2 | 02-01-2020  |            |
| Amazon  | en      |   5.4 | 01-01-2020  |            |
+---------+---------+-------+-------------+------------+

Output how it should be 

+------------+---------------+--------+-----------------+----------+----------------+
| product_no | product_price | amount | deliver_country |  datum   | marketingprice |
+------------+---------------+--------+-----------------+----------+----------------+
|       1001 |           2.8 |      2 | de              | 2-1-2020 |            1.2 |
|       1002 |           3.8 |      4 | en              | 3-1-2020 |            5.4 |
|       1001 |           2.8 |      1 | de              | 1-1-2020 |            3.2 |
+------------+---------------+--------+-----------------+----------+----------------+

In mysql I use this code :

b.start_date                <= date(i.system_created)                                           AND   
coalesce(b.end_date,now())  >= date(i.system_created)

Solution i found myself:

=QUERY(Marketing!$B$2:$E;IF(Marketing!E$2:E="";
"select E where  B ='"&D2&"' and D <=date'"&TEXT(E2;"yyyy-MM-dd")&"' and date'"&TEXT(VANDAAG();"yyyy-MM-dd")&"' >=date'"&TEXT(E2;"yyyy-MM-dd")&"' limit 1";
"select E where  B ='"&D2&"' and D <=date'"&TEXT(E2;"yyyy-MM-dd")&"' and E >=date'"&TEXT(E2;"yyyy-MM-dd")&"'"
);0)

Upvotes: 1

Views: 2720

Answers (2)

Jeff Rush
Jeff Rush

Reputation: 912

You could use Apps Script to solve this issue.

How does it work?

  1. Assign Sales data (D2:E7) to sales variable, Marketing data (B2:E7) to marketing variable.
  2. in for loop through sales and if sale_date < date_start set cell value as marketing_cost - 1

Below you can find a screenshot attached before and after.

function worker(){

  let ss = SpreadsheetApp.getActive();
  let sheetSales = ss.getSheetByName("Sales");
  let sheetMarketing = ss.getSheetByName("Marketing");

  // 1.
  let sales = sheetSales.getRange("D2:E7").getValues();
  /** sales
  [
    [de, 02-01-2020],
    [de, 03-01-2020],
    [de, 04-01-2020],
    [de, 06-01-2020],
    [de, 10-01-2020],
    [en, 10-01-2020]
  ]
  */
  let marketing = sheetMarketing.getRange("B2:E7").getValues();
  /** marketing
  [
    [de, 3.2, 01-01-2020, 02-01-2020],
    [de, 1.2, 03-01-2020, 04-01-2020],
    [de, 4.4, 05-01-2020, 06-01-2020],
    [de, 8.8, 07-01-2020, 08-01-2020],
    [de, 9.9, 09-01-2020, 25-02-2020],
    [en, 5.4, 01-01-2020, 25-02-2020]
  ]
  */

  let sale_date,
  date_start,
  date_end, 
  marketing_cost = [];

  for(let i = 0; i < sales.length; i++){

    sale_date = new Date(sales[i][1]);
    date_start = new Date(marketing[i][2]);
    date_end = new Date(marketing[i][3]);
    marketing_cost.push(marketing[i][1]);

    // 2.
    if(sale_date < date_start){
      sheetSales.getRange(2+i, 6).setValue(marketing_cost[i-1]);
    }else{
      sheetSales.getRange(2+i, 6).setValue(marketing_cost[i]);
    }
  }

}

Before:

enter image description here

After:

enter image description here

Reference:

Upvotes: 0

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFERROR(QUERY(
 {Marketing!B$2:D\ IF(Marketing!E$2:E=""; TODAY(); Marketing!E$2:E)}; 
 "select Col2 
  where Col1 = '"&D2&"' 
    and Col3 <= date '"&TEXT(E2;"yyyy-MM-dd")&"' 
    and Col4 <= "&TODAY()&"
  limit 1"; 0)))

Upvotes: 1

Related Questions