topic Error: Invalid expression in App Development

Error: Invalid expression

UgneKard — Sat, 16 Nov 2024 18:39:16 GMT

Hi,

Can anyone help with my script (I am a newbie on Qliksense) below.

I geting Error: Invalid expression, after loading this script . Thanks in advance.

tmp_item_ledger:
NOCONCATENATE LOAD
transaction.counter,
transaction.source_type,
item.#key,
FLOOR('$(g_date_max_history)' +1) AS calendar.#key,
LOWER(company &'|'& FLOOR('$(g_date_max_history)' +1) &'|'& currency.code) AS currency.#key,
currency.code,
entry_type AS %item_ledger.entry_type,
entry_subtype,
vendor.#key,
salesperson.#key,
location.#key,
item_variant.#key,
stock_keeping_unit.#key,
sales_price.#key,
location_visits.#key,
forecast.#key,
//TRIM(YEAR(calendar.#key)&NUM(MONTH(calendar.#key),00) &'|'& pfssalespers_purch_code) as salesperson_work_hours.#key,
TRIM(year(FLOOR(calendar.#key)) &'|'& month(floor(calendar.#key)) &'|'& location.#key) as salesperson_work_hours.#key,
global_dimension_1_code,
global_dimension_2_code,
veiklos_tipas,
pardavimu_kanalas,
input_date,
document_type,
item_variant.code,
source_type,
unit_of_measure_code,
correction,
company,
item.color,
item.size,
consider_for_toc_final_sale,
SUM(invoiced_quantity) AS stock.invoiced_quantity,
SUM(quantity) AS stock.quantity,
SUM(sales_amount) AS stock.sales_amount,
SUM(cost_amount) AS stock.cost_amount,
SUM(adjustment_cost_amount) AS stock.adjustment_cost_amount
RESIDENT tmp_value_entry
GROUP BY
transaction.counter,
transaction.source_type,
item.#key,
FLOOR('$(g_date_max_history)' +1),
LOWER(company &'|'& FLOOR('$(g_date_max_history)' +1) &'|'& currency.code),
currency.code,
entry_type,
entry_subtype,
vendor.#key,
salesperson.#key,
location.#key,
item_variant.#key,
stock_keeping_unit.#key,
sales_price.#key,
location_visits.#key,
forecast.#key,
TRIM(FLOOR(calendar.#key) &'|'& location.#key),
global_dimension_1_code,
global_dimension_2_code,
veiklos_tipas,
pardavimu_kanalas,
input_date,
document_type,
item_variant.code,
source_type,
unit_of_measure_code,
correction,
company,
item.color,
item.size,
consider_for_toc_final_sale;
DROP TABLE tmp_value_entry;

Re: Error: Invalid expression

fosuzuki — Wed, 13 May 2020 09:29:34 GMT

This part of code seems ok. What is the content of variable g_date_max_history?

Re: Error: Invalid expression

UgneKard — Wed, 13 May 2020 10:04:32 GMT

Content of variable g_date_max_history = DATE('2016.12.31').

Re: Error: Invalid expression

fosuzuki — Wed, 13 May 2020 12:11:06 GMT

So probably the issue is with the g_date_max_history output in this: FLOOR('$(g_date_max_history)' +1)

Does the content of the variable start with equal sign?

Re: Error: Invalid expression

UgneKard — Wed, 13 May 2020 13:29:50 GMT

No the variable contents only date value.

I added the attachment with variables values and the code which is write before this part.

Re: Error: Invalid expression

fosuzuki — Wed, 13 May 2020 14:08:12 GMT

From the details you sent, I think the variable should work fine.

I think that the 3 "columns" below doesn't need to be on the GROUP BY part. The first one is only a constant value, obviously not necessary, and the other two have fields already listed individually.

FLOOR('$(g_date_max_history)' +1),
LOWER(company &'|'& FLOOR('$(g_date_max_history)' +1) &'|'& currency.code),
TRIM(FLOOR(calendar.#key) &'|'& location.#key),

I'd recommend you to find the issue by commenting out some of the fields, reloading, and checking if the code works or not. Trial and error.