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
May be try it with Alt:
LET vWEEK1 = Alt(Date#(Replace(Replace(Peek('Week1', -1, 'Date1'),'WE',''),'.','/'), 'M.DD.YY'), Date#(Peek('Week1', -1, 'Date1'), 'MM/DD/YYYY hh:mm:ss'))
I tried to use you code, but still have the same result
So you are using variable because you are checking the format for a single date, right? or do you have a column with different date formats and you are trying to force them into a date format???
I'm using variable because date under main data and I can't load all necessary information by one load.
Date in 3 or 4 lines and data start from 9 line.
Date comes in different format from week to week (randomly) and I'm trying load date as Date each week without any manual work.
05/02/2015 09:44:35
05/09/2015 09:40:27
WE 6.06.15
Tried this script, seems to work:
Table1:
LOAD Date as Date1
FROM
Community_168133.xlsx
(ooxml, embedded labels, table is Sheet1);
LET vWEEK1 = Alt(Date(Date#(Trim(Replace(Peek('Date2', -1, 'Table2'),'WE','')), 'M.DD.YY'), 'MM/DD/YYYY'), Date(Peek('Date1', -1, 'Table1'), 'MM/DD/YYYY'));
Table2:
LOAD Date as Date2
FROM
Community_168133.xlsx
(ooxml, embedded labels, table is Sheet2);
LET vWEEK2 = Alt(Date(Date#(Trim(Replace(Peek('Date2', -1, 'Table2'),'WE','')), 'M.DD.YY'), 'MM/DD/YYYY'), Date(Peek('Date1', -1, 'Table1'), 'MM/DD/YYYY'));
Thank you for you help, but this doesn't work too(
Would you be able to share a sample where it doesn't work?
I want to load date with different format as Date.
For example I want to see following
And you script shows
That's what I asked you intially. I never touched the field itself. I was working on the variable. If you check the variable overview, you will see the correct format. So you need the field to be fixed.
Let me get you that.
Best,
Sunny