'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]

The variable outcomes are the same, you are just one day further than yesterday, so that's why they are +1 day. 

StartDate is a field and not a variable. So this shouldn't be the problem.

Load your field SalesDate in a table and the numbers in there should have the same format as your variables 43460, of course the numbers will be different. What is the outcome?

Jordy

Climber

[Edyta]

Could you assist on this as I am doing something wrong?

Newone:
Let vStartDate = floor(today()-9);
Let vEndDate = floor(today()-3);
LOAD
num(date#([SaleDate], 'YYYY-MM-DD')) as SaleDate,    <- is that what you meant?
[Index],
if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1) as Quantity
Resident [sprzedaż_czerwiec2019]
where floor([SaleDate]) >= $(vStartDate) and floor([SaleDate]) <= $(vEndDate);
Drop Table [sprzedaż_czerwiec2019];

[Edyta]

Is anyone who can assist and  point out why proper tabel is not returned?

what is wrong with date formats? ub my data file SaleDate is in format:

2019-06-05 12:59

[JordyWegman]

Edyta, check my file I've added. This example is working how you would like it.

Below you find the script. I've made an inline with 9 examples, but only three fall in between my where clauses. So they are returned, not the rest. Instead of the inline, use your own file and then this should work.

Let vStartDate = floor(today()-9);
Let vEndDate = floor(today()-3);

Newone:
Load
SalesDate,
Index,
Quantity
where floor([SalesDate]) >= $(vStartDate) and floor([SalesDate]) <= $(vEndDate);
;
Load
Date(Date#(SalesDate,'DD-MM-YY'),'DD-MM-YYYY') as SalesDate,
Index,
Quantity
;
Load * Inline [
SalesDate	,	Index	,	Quantity
18-06-19	,	1	,	2435
09-07-19	,	2	,	658
12-06-19	,	4	,	43
02-07-19	,	6	,	234
22-06-19	,	3	,	357
13-06-19	,	23	,	23
06-07-19	,	34	,	4346
16-06-19	,	5	,	47
21-06-19	,	66	,	123
];

Jordy

Climber 

[Edyta]

Can the format of SalesDate be the cause of not proper loading?

In Excel file the format is 'YYYY-MM-DD hh:mm' while in Qlik the SalesDate shows format 'DD.MM.YYYY hh:mm:ss'

I have changed the code according to format shown in Qlik:

Newone:
Let vStartDate = floor(today()-9);
Let vEndDate = floor(today()-3);
LOAD
Date(Date#([SalesDate], 'DD.MM.YYYY hh:mm:ss'), 'DD.MM.YYYY'),
[Index],
[Item],
if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1) as Quantity
Resident [Sales_June2019]
where floor([SalesDate]) >= $(vStartDate) and floor([SalesDate]) <= $(vEndDate);
Drop Table [Sales_June2019];

And it still shows whole table :(.

What is the reason you first showed name of column [SalesDate] adn later you formatted it? In my case it did not work too:(.

[Edyta]

Jordy,

big apologies - I have tried different codes on the same app and it did not refresh. I have checked it on a new app and it works. Thank you for your engagement.

[JordyWegman]

Great! No problem, please mark the post as solved! Success with your app.

Jordy

Climber