topic Re: How to take a Date header and insert in a column in QlikView

How to take a Date header and insert in a column

physical — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to take a Date header and insert in a column

sunny_talwar — Fri, 18 Aug 2017 11:54:18 GMT

May be load your data

Table

LOAD ...

FROM ....;

Join (Table)

LOAD ...

FROM ...

Where RowNo() = 1;

Re: How to take a Date header and insert in a column

PrashantSangle — Fri, 18 Aug 2017 11:57:23 GMT

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,

Re: How to take a Date header and insert in a column

physical — Fri, 18 Aug 2017 12:52:03 GMT

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

Re: How to take a Date header and insert in a column

sunny_talwar — Fri, 18 Aug 2017 13:30:33 GMT

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?

Re: How to take a Date header and insert in a column

physical — Fri, 18 Aug 2017 14:08:50 GMT

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

Re: How to take a Date header and insert in a column

sunny_talwar — Fri, 18 Aug 2017 16:15:19 GMT

Can you just add the files here?

Uploading a Sample

Re: How to take a Date header and insert in a column

physical — Wed, 30 Aug 2017 10:46:57 GMT

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

Mike

Re: How to take a Date header and insert in a column

sunny_talwar — Wed, 30 Aug 2017 15:33:18 GMT

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

Re: How to take a Date header and insert in a column

physical — Wed, 30 Aug 2017 15:53:37 GMT

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.

Re: How to take a Date header and insert in a column

MarcoWedel — Wed, 30 Aug 2017 20:39:33 GMT

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

Re: How to take a Date header and insert in a column

physical — Thu, 31 Aug 2017 12:32:16 GMT

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.