Loading with date condition in where

[rakeshshah]

Hi

I am sure this answer is sooo simple - im loading the same table twice, the first time to get a the max date (so i can calculate 30 days from the max date), the second time is to filter for only the last 30 days (using the max date).

My question is how do i get the final where condition to work. I've tried various combinations (see option1 and option2) - but nothing loads. Also when I used hard coded value eg '16/10/2017' it worked.

Please help

//Get Max import date

tempMax35Date:

Load

date(max(Timestamp(Timestamp#([Last Contact (ymdhms)], 'YYYY-MM-DD-hh-mm-ss"') ,'DD/MM/YYYY'))-30) AS [tempLastSeen]

FROM [lib://Asset Files/Auto Imports\Avalanche_3.5_clients.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

//assign to variable - I have tried option 1 and option 2

//option 1

let tempas=date(fieldvalue('tempLastSeen',1),'DD/MM/YYYY');

//option 2

let tempas=fieldvalue('tempLastSeen',1);

//just checking its right

trace $(tempas);

//loading the data and hoping the where condition works

[Avalanche_3.5_clients]:

LOAD

[MAC Address],

[Reported IP],

    left([Reported IP],FindOneOf([Reported IP],'.',3)) as tempIP,

    Timestamp(floor(Timestamp#([Last Contact (ymdhms)], 'YYYY-MM-DD-hh-mm-ss"')) ,'DD/MM/YYYY') as [Last Seen]  

FROM [lib://Asset Files/Auto Imports\Avalanche_3.5_clients.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq)

// Option 1

where Timestamp(Floor(Timestamp#([Last Contact (ymdhms)], 'YYYY-MM-DD-hh-mm-ss"')),'DD/MM/YYYY')>date($(tempas),'DD/MM/YYYY')

//Option 2

//where Timestamp(Floor(Timestamp#([Last Contact (ymdhms)], 'YYYY-MM-DD-hh-mm-ss"')),'DD/MM/YYYY')>$(tempas)

;

[jonathandienst · Accepted solution]

Do the comparison as a numeric to avoid being tripped up by the format of the $ expansion:

tempMax35Date:

Load

  Timestamp#([Last Contact (ymdhms)], 'YYYY-MM-DD-hh-mm-ss') AS tempLastSeen

FROM [lib://Asset Files/Auto Imports\Avalanche_3.5_clients.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

//assign to variable - I have tried option 1 and option 2

//option 1

Let tempas = Num(Peek('tempLastSeen')) - 30;

[Avalanche_3.5_clients]:

LOAD

  [MAC Address],

  [Reported IP],

    left([Reported IP],FindOneOf([Reported IP],'.',3)) as tempIP,

    Date(floor(Timestamp#([Last Contact (ymdhms)], 'YYYY-MM-DD-hh-mm-ss"')) ,'DD/MM/YYYY') as [Last Seen]

FROM [lib://Asset Files/Auto Imports\Avalanche_3.5_clients.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq)

where Timestamp#([Last Contact (ymdhms)], 'YYYY-MM-DD-hh-mm-ss') > $(tempas);

[rakeshshah]

Thank you - using the num seems to have fixed it - thanks

Rakesh