How to take a Date header and insert in a column

[physical]

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

[MarcoWedel · Accepted solution]

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

[physical]

Brilliant, you lovely person. This will be used in son many places, Thank you.

Can I throw a further request at you - relevant to the previous, similar but I think different. Before you accept/reject I try to give you the problem(:

The document has Headers like: 'Current Aug', '1 Month Jul', '2 Month Aug', '3 Month Sep', 'History', 'BLANK', 'BLANK'.etc

I manually edit to remove 'Aug', '1 & Aug'.etc

To leave these as headers each and every time: Current, Month1, Month2, Month3, H1, H2, H3 & H4

Note the last 3 headers are added as they are blank.

If you choose to except this mission(: you'll have educated me for so much more. thank you in advance, which ever decision you make.