'Start Date not found'

[Edyta]

Hi guys,

I have the code as below:

Newone:
SET DateFormat='DD.MM.YYYY';
Let vStartDate = Date(today()-8, 'DD.MM.YYYY');
Let vEndDate = Date(today()-2, 'DD.MM.YYYY');
LOAD
Date([SalesDate]),
[Index],
[Item],
if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1)
Resident [June_sales]
where [SalesDate] >= vStartDate and [SalesDate] <= vEndDate;
Drop Table [June_sales];

I get the comment that field vStartDate is not found. What is wrong with this code?

[JordyWegman]

Hi,

If you want to call a variable, you need to use $().

Newone:
SET DateFormat='DD.MM.YYYY';
Let vStartDate = Date(today()-8, 'DD.MM.YYYY');
Let vEndDate = Date(today()-2, 'DD.MM.YYYY');
LOAD
Date([SalesDate]) as SalesDate,
[Index],
[Item],
if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1) as Quantity
Resident [June_sales]
where [SalesDate] >= $(vStartDate) and [SalesDate] <= $(vEndDate);
Drop Table [June_sales];

I've edited your script, try this.

Jordy

Climber

[Edyta]

Unfortunately, still variable vStartDate is not found.

[Ivan_Bozov]

Can you try to set it without the date formatting:

LET vStartDate = TODAY()-8;

And, of course, use the dollar sign as Jordy suggested already.

[Edyta]

Previously I have not noticed parantheses with $ sign. Now I change it.

Newone:
SET DateFormat='DD.MM.YYYY';
Let vStartDate = Date(today()-8, 'DD.MM.YYYY');
Let vEndDate = Date(today()-2, 'DD.MM.YYYY');
LOAD
Date( SaleDate) as SaleDate,

 [Index],
[Item],
if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1) as Quantity
Resident June_sale
where [SaleDate] >= $(vStartDate) and [SaleDate] <= $(vEndDate);
Drop Table June_sale;

And the comment is:

Unexpected token: '.2019', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...

what is wrong with start date?

Qlik returns the comment:

where [Data realizacji] >= 17.06>>>>>>.2019<<<<<< and [Data realizacji] <= 23.06.2019

[JordyWegman]

This is probably due to the fact that you set a number format.

My advice would be to always use floored numbers 43631 for example. This way you will always have the right numbers.

Try this:

Let vStartDate = floor(today()-8);
Let vEndDate = floor(today()-2);

Newone:
LOAD
floor([SalesDate]) as SalesDate,
[Index],
[Item],
if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1) as Quantity
Resident [June_sales]
where floor([SalesDate]) >= $(vStartDate) and floor([SalesDate]) <= $(vEndDate);
Drop Table [June_sales];

Jordy

Climber

[Ivan_Bozov]

---

where [Data realizacji] >= 17.06>>>>>>.2019<<<<<< and [Data realizacji] <= 23.06.2019

---

This is due to the way you set the date. Please see my previous comment and try that.

[Edyta]

seems like it works but I get as a result whole table with all data not the selected data. Despite the the fact that the new table (called Newone) has been created.

Why it does not show selected data?

15:19:21

Newone << June_sale

[JordyWegman]

It seems then that your variables are not working correctly.

Check your variable overview and see what's inside vStartDate and vEndDate. You should see the numbers 43633 and 43639. Is that correct?

Jordy

Climber

[Edyta]

variables attached. The values differ.

No idea why are two names: name and current name.

the name of table is sprzedaz_czerwiec2019.

SaleDate is in format 2019-06-05 12:59, I have specified format in code in code without the time. Can in be the cause of error?