Creating Quarters From Given Months

[kaygee28]

Good Day, I know this question might have been asked before but I cant seem to make my app work from this.

I have four excel spreadsheets that I have concatenated to the first table that I uploaded and I need to create a quarter for each of those months that are on the consolidated table.

I.e months 1-12 need to be divided into quarters and what  I currently have is this

Directory;

LOAD [Contact ID],

     Moved,

     [Current Owner of BMW],

     [Previous Owner],

     [Vehicle Model],

[Year Purchased],

     [Month Purchased],

FROM

[X5 New Updated Data\BMW All.xlsx]

(ooxml, embedded labels, table is Sheet1);

Rename table [Sheet1] to [BMW X5 Buyers];

if([Month Purchased]([BMW X5 Buyers])<4,'Q1',

               if([Month Purchased]([BMW X5 Buyers])<7,'Q2',

               if([Month Purchased]([BMW X5 Buyers])<10,'Q3','Q4'))) AS Quarter;

from then on all the other tables are then concatenated to the first table etc

Directory;

CONCATENATE([BMW X5 Buyers])

LOAD [Contact ID],

     [Previous Owner],

     [Vehicle Model],

     [Vehicle Version],

     [Vehicle Model Description],

     [Year Purchased],

     [Month Purchased],

FROM

[X5 New Updated Data\BMW X5 No Longer BMW Owner.xlsx]

(ooxml, embedded labels, table is Sheet1);

Directory;

CONCATENATE([BMW X5 Buyers])

LOAD [Contact ID],

     [Current Owner of BMW],

     [Previous Owner],

     [Vehicle Model],

     [Vehicle Version],

     [Vehicle Model Description],

     [Year Purchased],

     [Month Purchased],

FROM

[X5 New Updated Data\BMW 1st Time Drivers.xlsx]

(ooxml, embedded labels, table is Sheet1);

.

.

.

.etc etc

the current error I get is this

Syntax error, missing/misplaced FROM:

LOAD [Contact ID],

[kaygee28]

Solved thank you, perserverance was key.

All I did was this:

Directory;

LOAD [Contact ID],

     Moved,

     [Current Owner of BMW],

     [Previous Owner],

     [Vehicle Model],

[Year Purchased],

     [Month Purchased],

if([Month Purchased]<4,'Q1',

               if([Month Purchased]<7,'Q2',

               if([Month Purchased]<10,'Q3','Q4'))) AS Quarter

FROM

[X5 New Updated Data\BMW All.xlsx]

(ooxml, embedded labels, table is Sheet1);

Rename table [Sheet1] to [BMW X5 Buyers];

Then I concatenated the other tables.

[Gysbert_Wassenaar · Accepted solution]

Remove the comma after the last field: [Month Purchased] , <-

You can create the quarters easier like this: 'Q' & Ceil([Month Purchased]/3) as Quarter

[petter]

The normal practice to calculate quarter is:

'Q' & Ceil( [Month Purchased] / 3 ) 

This will simplify your rather lengthy expression. 

If you prefer to keep your nested if expression then you should have a look at this part:

     [Month Purchased]([BMW X5 Buyers])   

I don't see how QlikView would be able to interpret that. You should reference a field directly and there is really no way in QlikView to qualify a field with the table reference - unless you actually rename the field so it becomes a longer field name containing the table name.

[rwunderlich]

You can simplify the script as follows:

[BMW X5 Buyers]:

LOAD [Contact ID],

     Moved,

     [Current Owner of BMW],

     [Previous Owner],

     [Vehicle Model],

[Year Purchased],

     [Month Purchased],

    'Q' & Ceil( [Month Purchased] / 3 )  as Quarter

FROM

[X5 New Updated Data\BMW All.xlsx]

(ooxml, embedded labels, table is Sheet1);

[kaygee28]

Thanks everyone for your help I really appreciate it.