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

[#1]

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

[sunny_talwar · Accepted solution]

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;

[Gysbert_Wassenaar]

Try something like this:

LOAD

     [Fiscal Year and Quarter],

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

     ...other fields...

FROM ....

[#4]

Thanks – that works perfectly ☺

Regards,

Graham

[sunny_talwar]

Not a problem

I am glad I was able to help.

Best,

Sunny

[#6]

Thanks Gysbert – much appreciated ☺

Regards,

Graham

[sunny_talwar]

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

[#8]

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

[sunny_talwar]

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