topic Re: Adding a column while loading data in App Development

Adding a column while loading data

kailashkotak91 — Wed, 04 Apr 2018 20:06:24 GMT

Hello,

So I am loading data from 12 different files (1file for each month). However the file does not have any specific date/month/time attribute. I want to add a static column with Month Name. Following is my sample of my Load. Can someone help me with adding a column while i load the data ?

Load

First Name

Last Name

Grade

Request Number

Unique ID

Month (How to add this with a static value of January)

From

abc.xls

Can someone help me with adding the month column as shown above with a static value ?

Regards,

Kailash Kotak

Re: Adding a column while loading data

CarlosAMonroy — Wed, 04 Apr 2018 22:20:02 GMT

Hi Kailash,

Why do'nt you just add the text. Like this:

Load

First Name

Last Name

Grade

Request Number

Unique ID

Date('1/01/2018','MM/DD/YYYY') as Date,

//or Just get the Month for that Static date

Month(Date('1/01/2018','MM/DD/YYYY')) as Month

From

abc.xls

Hope that helps,

Carlos M

Re: Adding a column while loading data

Digvijay_Singh — Wed, 04 Apr 2018 22:44:18 GMT

Or you may want to have month name from your filename if your individual filename has month name. This will be dynamic and same code will give you actual month name column for your data file.

LOAD *, Subfield(FileName(),'_',2) as Month from

C:\UserFiles\abc_Jan18.txt

Will return 'Jan18' in field Month.

Re: Adding a column while loading data

kailashkotak91 — Thu, 05 Apr 2018 16:09:14 GMT

Yes it does!

If it is not month.. I wanted to add one more column that is not part of the data set. Region, I want region to have a single static va

Load

First Name

Last Name

Grade

Request Number

Unique ID

Region('Japan') as Region

From

abc.xls

So will this work ?

Re: Adding a column while loading data

CarlosAMonroy — Thu, 05 Apr 2018 16:35:05 GMT

Just do:

'Japan' as Region

The previous 'Date' and 'Month' are functions that convert a data field to that specific format.

You do not have to specify the format for the field Region as it's already text.

Carlos M

Re: Adding a column while loading data

kailashkotak91 — Thu, 05 Apr 2018 19:41:02 GMT

Got it!

Thanks a lot!

This solves a lot of problem for me.

Regards,

Kailash Kotak