2 Replies Latest reply: Oct 16, 2017 1:18 AM by Rakesh Shah RSS

    Loading with date condition in where

    Rakesh Shah

      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)

       

       

      ;

        • Re: Loading with date condition in where
          Jonathan Dienst

          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);