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)
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)))