Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Created on 18/08/17 at 08:35 by mike
Hi, I have be trying for too long unsuccessfully and finally need to call for some assistance please:
Many of the report I pull into QV start with the above statement referencing the Date and time.
I need to create two data column showing the Date and time respectively, filling the column where fields have corresponding data.
Two images of what I am trying to achieve are below: Mark Yellow data is what I need in the columns.
What I would like to achieve.
Your help very much appreciated
Hi,
one possible solution:
table1:
LOAD Date#(TextBetween(@1,' on ',' at '),'DD/MM/YY') as Date,
Time#(TextBetween(@1,' at ',' by '),'hh:mm') as Time
FROM [https://community.qlik.com/servlet/JiveServlet/downloadBody/19012-102-1-25664/cred-contrl.xls] (biff, no labels, table is [cred-contrl$])
Where @1 like 'Created on*at*by*';
Join
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/downloadBody/19012-102-1-25664/cred-contrl.xls] (biff, embedded labels, header is 4 lines, table is [cred-contrl$]);
hope this helps
regards
Marco
May be load your data
Table
LOAD ...
FROM ....;
Join (Table)
LOAD ...
FROM ...
Where RowNo() = 1;
1st line format is always like this????
You can try like
Existing load script;
Join
Load subfield(subfield(fieldName,'at',1),' ',-1) as date
subfield(fieldName,'at',2,' ',1)as time
from table;
Regards,
Thanks for the quick reply unexpected, not sure how to script this, would you be kind enough to upload a example .qvw or am I asking too much(:
No you are not... would you be able to share the Excel file with may be just one single row of dummy data in the same format as above?
No problem, here are two versions of the same Excel and CSV, there are many files I import and would use this solution on all:
Hope you call get these from Dropbox, other do you have an email?
Thank you
Mike
The moderator deleted the post as it was a duplicate of the existing (not allowed and correct to delete)
supplied instructions for using the edit option and attaching a file, finally getting there with some basic's, please see attached.
Point to note though, there is no 'action' option on my original post!
Mike
Can you just add the files here?
Hi Sunny, have finally posted a new discussion with attachment, hopefully you'll see, thanks for your patience.
Mike
Where have you posted it? Is it still under moderation?
Good question, the moderator just approved it and your email followed, but cannot now see the new discussion I created with attachment, I copied the details exactly, this may have caused a fudge up.