Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How can I create a new field from existing data in the Data Load Editor?

Hi,

I am loading an Excel file from SharePoint with around 50 columns of data and around 7,000 records. One of the data fields is named 'Fiscal Year and Quarter' and contains 11 characters, ie 'FY2015 Qtr3'.

I need to extract just the first 6 characters from this field, ie 'FY2015', and not the Quarter, and I want to create a new field named 'Fiscal Year' containing these 6 characters.

Can someone please let me know how I can set a command in the Data Load Editor to do this?

Thanks

1 Solution

Accepted Solutions

Re: How can I create a new field from existing data in the Data Load Editor?

Try this:

LOAD [Fiscal Year and Quarter],

          Left([Fiscal Year and Quarter], 6) as [Fiscal Year],

          Right([Fiscal Year and Quarter], 4) as [Fiscal Quarter]

          OtherFields

From Source;

8 Replies

Re: How can I create a new field from existing data in the Data Load Editor?

Try this:

LOAD [Fiscal Year and Quarter],

          Left([Fiscal Year and Quarter], 6) as [Fiscal Year],

          Right([Fiscal Year and Quarter], 4) as [Fiscal Quarter]

          OtherFields

From Source;

Re: How can I create a new field from existing data in the Data Load Editor?

Try something like this:

LOAD

     [Fiscal Year and Quarter],

     SubField([Fiscal Year and Quarter],' ',1) As [Fiscal Year],

     ...other fields...

FROM ....


talk is cheap, supply exceeds demand
Not applicable

Re: How can I create a new field from existing data in the Data Load Editor?

Thanks – that works perfectly ☺

Regards,

Graham

Re: How can I create a new field from existing data in the Data Load Editor?

Not a problem

I am glad I was able to help.

Best,

Sunny

Not applicable

Re: How can I create a new field from existing data in the Data Load Editor?

Thanks Gysbert – much appreciated ☺

Regards,

Graham

Re: How can I create a new field from existing data in the Data Load Editor?

Graham, if you got what you were looking for, I suggest closing this thread by marking the correct answer and any helpful answers.

Best,

Sunny

Not applicable

Re: How can I create a new field from existing data in the Data Load Editor?

Hi Sunny,

I’ve tried to do that but I cannot find an option to allow me to mark it as answered correctly. When I click on ‘Actions’ at the bottom of your mail in Qlik Community, a box pops up saying ‘No actions are available’ and I cannot see any other way to mark this as answered in the Discussions Forum.

Can you advise?

Regards,

Graham

Re: How can I create a new field from existing data in the Data Load Editor?

Going to the actual thread may give you the options: Re: How can I create a new field from existing data in the Data Load Editor?

See if that helps

Community Browser