Solved: How to take a Date header and insert in a column - Qlik Community

physical · ‎2017-08-18

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 · ‎2017-08-30

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

View solution in original post

sunny_talwar · ‎2017-08-18

May be load your data

Table

LOAD ...

FROM ....;

Join (Table)

LOAD ...

FROM ...

Where RowNo() = 1;

PrashantSangle · ‎2017-08-18

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

physical · ‎2017-08-18

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(:

sunny_talwar · ‎2017-08-18

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?

physical · ‎2017-08-18

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:

Give feedback

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

sunny_talwar · ‎2017-08-18

Can you just add the files here?

Uploading a Sample

physical · ‎2017-08-30

Hi Sunny, have finally posted a new discussion with attachment, hopefully you'll see, thanks for your patience.

Mike

sunny_talwar · ‎2017-08-30

Where have you posted it? Is it still under moderation?

physical · ‎2017-08-30

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.