help with if statement in script variables

[ioannagr]

Hi, I just wanted you to see what I have come up with this script bit. As you can see from my notes, I want to load the max timestamp in my data then compare it to now timestamp and  load the data according to this vDate IF statement I did.

Max_Date:

Load timestamp(max(DayStart([date timestamp])), 'DD/MM/YYYY hh:mm:ss.ffffff') as MaxDate //with this i want to load the max date timestamp in my dataset with 00:00:00.00000 timestamp
from [$(vPathQVD)Sales.qvd] (qvd);

let vNow = Now(); // with this i want to get today's timestamp

Let vCorrectDate=If(MaxDate<vNow, date(floor(MaxDate-1)), date(floor(MaxDate)));  // with this i want to compare timestamp MaxDate with Now() timestamp and if maxDate<vNow holds, then return me the previous of the MaxDate floored, else floored MaxDate

sales:
LOAD
"ID product",
"amount sold",
"availability"
"date timestamp"
FROM [$(vPathQVD)Sales.qvd]
(qvd)
where Date(Floor("date timestamp"),'DD/MM/YYYY') <='$(vDate)';  //so as to load data according to vCorrectDate

Is my thinking correct? Do you see any mistakes? Should I add something else?

Thank you in advance.

[ioannagr]

This loads 0 sales, so something is off. 

Please help!

[MayilVahanan]

Hi @ioannagr 

Max_Date:

Load timestamp(max(DayStart([date timestamp])), 'DD/MM/YYYY hh:mm:ss.ffffff') as MaxDate //with this i want to load the max date timestamp in my dataset with 00:00:00.00000 timestamp

from [$(vPathQVD)Sales.qvd] (qvd);

LET vMaxDate = peek('MaxDate');

let vNow = Now(); // with this i want to get today's timestamp

Then u can use vMaxDate variable for compare and load the data.

And u are storing the compare variable in vCorrectDate . So u need to use vCorrectDate

In where condition and also use floor in both side in where condition.

[ioannagr]

So you suggest correcting vCorrectDate like this?

Let vCorrectDate=If(vMaxDate<vNow, date(floor(vMaxDate-1)), date(floor(vMaxDate)));

can you provide me with the correct way of using floor on both sides? @MayilVahanan 

Thank you loads!!!!

[Gabriel]

@ioannagr  what @MayilVahanan is saying is

1st pass the value in MaxDate into a variable called vMaxDate, then use the variable vMaxDate in 

Let vCorrectDate=If($(vMaxDate)<vNow, date(floor($(vMaxDate)-1)), date(floor($(vMaxDate))));

[ioannagr]

Hi guys @Gabriel  @MayilVahanan 

2 more questions:

1)why don't i use $ when calling vNow in vCorrectDate?

2)Is my where clause correct like that ?

where Date(Floor(timestamp(date([date timestamp]),'DD/MM/YYYY hh:mm:ss.ffffff')),'DD/MM/YYYY') <='$(vCorrectDate)';

[MayilVahanan]

Hi @ioannagr 

can you provide the sample data , will check and provide the syntax..

[ioannagr]

So far i have

Max_Date:

Load timestamp(max([date timestamp]), 'DD/MM/YYYY hh:mm:ss.ffffff') as MaxDate
from [$(vPathQVD)Sales.qvd] (qvd);

LET vMaxDate = peek('MaxDate');

LET vNow = Date(Daystart(Now(),'DD/MM/YYYY hh:mm:ss.ffffff')); 

LET vCorrectDate=If($(vMaxDate)<$(vNow), date(floor($(vMaxDate)-1),'DD/MM/YYYY'), date(floor($(vMaxDate)),'DD/MM/YYYY'));

Sales:
sales:
LOAD
"ID product",
"amount sold",
"availability"
"date timestamp"
FROM [$(vPathQVD)Sales.qvd]
(qvd)
where Date(Floor("date timestamp"),'DD/MM/YYYY') <='$(vCorrectDate)';  

But it is not working, @MayilVahanan . Not allowed to share any data 😞 Your help is  much needed!!! Gives me an error at vCorrectDate, like 

The error occurred here:

LET vCorrectDate=If(06/11/2020 >>>>>>00<<<<<<:00:14.299000<, date(floor(06/11/2020 00:00:14.299000-1),'DD/MM/YYYY'), date(floor(06/11/2020 00:00:14.299000),'DD/MM/YYYY'))

[MayilVahanan]

HI @ioannagr 

Try like this

Max_Date:

Load max([date timestamp]) as MaxDate
from [$(vPathQVD)Sales.qvd] (qvd);

LET vMaxDate = floor(peek('MaxDate'));

LET vNow = floor(Now()); 

LET vCorrectDate=If($(vMaxDate)<$(vNow), floor($(vMaxDate)-1), floor($(vMaxDate)));

Sales:
sales:
LOAD
"ID product",
"amount sold",
"availability"
"date timestamp"
FROM [$(vPathQVD)Sales.qvd]
(qvd)
where Floor("date timestamp") <=$(vCorrectDate);  

[ioannagr]

hi @MayilVahanan this works, thank you.

I need to understand why though so as to gain knowledge.

Why did you floor peek(MaxDate)  (LET vMaxDate = floor(peek('MaxDate') )

and then again floor  it at LET vCorrectDate=If($(vMaxDate)<$(vNow), floor($(vMaxDate)-1), floor($(vMaxDate)))?

Also why were formats causing problems?

Thank you in advance 😄