Date range error

[Edyta]

Hi guys,

I'm strugling with occuring error in 10th line. Could you help me to solve the problem?

I need to get data from 25/03/2019 to 31/03/2019.

1 Let StartDate=date('25/03/219', 'DD/MM/YYYY');
2 Let EndDate=date('31/03/2019', 'DD/MM/YYYY');
3 Newone:
4 LOAD
5 [SaleDate],
6 [Index],
7 [Item],
8 if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1) as QuantityKG
9 Resident sale;
10 where [SaleDate] >='$(StartDate)' and [SaleDate]<='$(EndDate)';  <- here I get a comment

Unknown statement: where [SaleDate] >='' and [SaleDate]<=''

11 Drop Table sale;

[Anil_Babu_Samineni]

1) Check the format how it is returning in variable by help of Variable overview?

2) Date format is wrong in vStartDate 

            Let StartDate=date('25/03/2019', 'DD/MM/YYYY');

3) You have close before statement only

Newone:
LOAD
[SaleDate],
[Index],
[Item],
if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1) as QuantityKG
Resident sale; // Remove this
where [SaleDate] >='$(StartDate)' and [SaleDate]<='$(EndDate)';

[Edyta]

Still error, now I get a comment "Duplicate Derived Field" and then "Derive fields from fields [SaleDate] using [autocalendar]. No Idea what is wrong. Any suggestions?

[Anil_Babu_Samineni]

Can you please paste this table as well? "Resident sale" how you have loaded?

[Edyta]

Sorry for the late response, I thought  I have sent it.

Below is the table. Actually, SaleDate is with time and I adapted the code.

SaleDate	Index	Item	Quantity
2019-03-01 00:06	456543	X	-4665
2019-03-01 00:06	321333	Y	-4632
2019-03-01 00:25	649879	Z	-76544

The code I have now is:

Let StartDate=date('25/03/2019', 'DD/MM/YYYY');
Let EndDate=date('31/03/2019', 'DD/MM/YYYY');
Newone:
LOAD
Date(Floor(TimeStamp#([SaleDate],'YYYY-MM-DD hh:mm')), 'DD/MM/YYYY'),
[Index],
[Item] ,
if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1)
Resident sales
where SaleDate >='$(StartDate)' and SaleDate <='$(EndDate)';
Drop Table sales;

Concatenate (Newone)
LOAD Sum([Quantity]) as Quantity
Resident Newone;

The error I get is: field 'SaleDate' not found

[sunny_talwar]

Few questions

1) Why are you doing this? It seems both true and false condition are multiplying the quantity by -1?

if("[Quantity]">0, "[Quantity]"*-1, "[Quantity]"*-1)

2) Can you share the complete script or logfile? If the error is 'SaleDate' not found, then sales table might not have a field called SaleDate... or it might be called something else? or sales table is not called sales, but something else... without knowing what it is, it would be difficult to know.