Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Check out the attached qvw.
Best,
Sunny