topic Re: Creating Quarters From Given Months in QlikView

Creating Quarters From Given Months

kaygee28 — Thu, 18 Feb 2016 07:37:35 GMT

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],

Re: Creating Quarters From Given Months

kaygee28 — Thu, 18 Feb 2016 13:18:47 GMT

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.

Re: Creating Quarters From Given Months

Gysbert_Wassenaar — Thu, 18 Feb 2016 17:27:40 GMT

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

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

Re: Creating Quarters From Given Months

petter — Thu, 18 Feb 2016 17:33:55 GMT

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.

Re: Creating Quarters From Given Months

rwunderlich — Thu, 18 Feb 2016 17:41:59 GMT

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);

Re: Creating Quarters From Given Months

kaygee28 — Thu, 18 Feb 2016 18:00:13 GMT

Thanks everyone for your help I really appreciate it.