10 Replies Latest reply: Jun 12, 2015 12:33 PM by Sunny Talwar RSS

    How load date from file?

    Виталий Чуприна

      Hi all,

       

      I received files and need to load data from them, but have some problems:

      1. Data located in different columns and columns(A-C) and lines (3-4)

      I use following code for line 3 and the same for line 4 (WHERE recno() = 2; )

      How check two lines by one load?

       

      LOAD

          num(subfield(rtrim(A & ' ' & B & ' ' & C & ' ' & D ), ' ', -1)) AS Week1,

      FROM [$(vFile)]  (ooxml, no labels, header is 2 lines, table is [$(vSheetName)])

      WHERE recno() = 1;

       

      Untitled1.pngUntitled.png

       

      2. From time to time data has different format (see screenshot), for example

       

      05/02/2015 09:44:35

      WE 6.06.15

       

      I tried use following code to load data to variable

       

      LET vWEEK1 = replace(replace(peek('Week1', -1, 'Date1'),'WE',''),'.','/');

      .

      .

      .

      weekstart(date('$(vWEEK1)','YYYYMMDD'),0,-2) AS Week

       

      But in this case for some reason I lose data in date format (05/02/2015 09:44:35) and load only WE 6.06.15,


      if I use

       

      LET vWEEK1 =(peek('Week1', -1, 'Date1') 

      I load only 05/02/2015 09:44:35 and lose WE 6.06.15,



      Also I tried use 'IF' , but it doesn't work correctly and return only WE 6.06.15 data  :


      LET vWEEK1 = IF (Wildmatch(peek('Week1', -1, 'Date1'), '*WE*'), replace(replace(peek('Week1', -1, 'Date1'),'WE',''),'.','/'),peek('Week1', -1, 'Date1'));


      Thanks,

      Vitaliy