How load date from file?

[vchuprina]

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;

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

[sunny_talwar · Accepted solution]

Check out the attached qvw.

Best,

Sunny