Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
11 Drop Table sale;
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)';
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?
Can you please paste this table as well? "Resident sale" how you have loaded?
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
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.