Collating Months in Quarters

[#1]

Hello All,

I have a number of tables which have been concatenated. I have then used the FileBaseName() function to retrieve the Excel filename and, thus, identify the month the data pertains to.

Would it be possible to organise this data into a quarter? If so, please let me know.

All the best,

Alison

P.S

If it's possible, please can provide a description of how the solution works as if I am just provided with the function, I find it difficult to understand how and why it works.

[sunny_talwar]

Did you try this?

'Q' & Ceil(Month(MonthYearField)/3) as Quarter

I explained what it is doing... does the explanation doesn't make sense?

Re: Collating Months in Quarters

[prashanth1712]

As sunny said in his first comment, try adding master calender in your script, all your dates related issues will be resolved.

[passionate]

You can even use Mapping load to find Quarters

[#5]

Thank you for you numerous responses. Please can you tell me, where should I place this expression?

[sunny_talwar]

In the script where you have Month field

[#7]

I haven't defined the month field.

I have only have a field that I've generated from the File Base Name, which is called MM-YYYY and holds that value. It is potentially not being recognised as a numeric value which might be causing trouble.

[sunny_talwar]

If you don't mind sharing, can you show how exactly are you using FileBaseName() function to get MM-YYYY?

[#9]

Yeah no problem. I loaded a number of tables which share field names and so auto-concatenated. Each table pertains to a different month and so I created a field name out of the excel file name. Below is replicated for each table:

[Sheet1_d9c0e355-4eb4-8421-c01b-49f204a2]:
LOAD [Employee ID],
[Gender],
[Current Job Title],
    mid(FileBaseName(), 1, 😎 as [MM-YYYY]
   
FROM [lib://Demo/01-2017 Organisation Name .xlsx]
(ooxml, embedded labels, table is Sheet1);

RENAME TABLE [Sheet1_d9c0e355-4eb4-8421-c01b-49f204a2] TO [Sheet1];
RENAME TABLE [Sheet1] TO [(Sheet1...)];

[sunny_talwar]

For example, if you are doing this today

SubField(FileBaseName(), '_', -1) as Month

Then you can do this for the Quarter

'Q' & Ceil(Month(Date#(SubField(FileBaseName(), '_', -1), 'MM-YYYY'))/3) as Quarter

Also, for your future reference, I would really encourage you to read this thread
How to get answers to your post?

[sunny_talwar]

Try this

[Sheet1_d9c0e355-4eb4-8421-c01b-49f204a2]:
LOAD [Employee ID],
     [Gender],
     [Current Job Title],
     Mid(FileBaseName(), 1, 😎 as [MM-YYYY],

     'Q' & Ceil(Month(Date#(Mid(FileBaseName(), 1, 😎, 'MM-YYYY'))/3) as Quarter
FROM [lib://Demo/01-2017 Organisation Name .xlsx]
(ooxml, embedded labels, table is Sheet1);

RENAME TABLE [Sheet1_d9c0e355-4eb4-8421-c01b-49f204a2] TO [Sheet1];
RENAME TABLE [Sheet1] TO [(Sheet1...)];