Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
;
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);
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);
Thank you - using the num seems to have fixed it - thanks
Rakesh