Help with logic

[#1]

I am trying to display a sum of quantity sold within a set of ranges, and I am not getting the results that I need. 

Here are the ranges:

0-11

12-19

20-29

30-39

40-79

80+

My primary data is as follows:

'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Location Number] as 'Location_Number',

[Primary Brand] as 'Brand',

[Invoice Extended Amount] as 'Net_Sales',

[Carton Equivalent] as 'QVOF'

I am using the following to classify the ranges within the load script, but I am not getting the unique count by location since the load script is using the calendar date.

Carton_Bucket:

LOAD

[CAGE Region] as 'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Primary Brand] as 'Brand',

[Location Number] as 'Location_Number',

If(([Carton Equivalent]<12),'<12',If(([Carton Equivalent]>=12 and [Carton Equivalent]<20),'12-19',

If(([Carton Equivalent]>=20 and [Carton Equivalent]<30),'20-29',If(([Carton Equivalent]>=30 and [Carton Equivalent]<40),'30-39',

If(([Carton Equivalent]>=40 and [Carton Equivalent]<80),'40-79','80+'))))) as 'Carton_Bucket'

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

Calendar Date    Location_Number   Carton Equivalent

3/1/12                    123                             2

3/2/12                    123                             4

3/3/12                    123                             20

The load script is generating the following for location 123 (6 cartons in the 0-11 range and 20 cartons in the 20-29 range)

What I am looking for is 26 cartons total in the 20-29 range (total cartons for each location regardless of day), but I cannot figure out how to get this work correctly.

Any ideas on what I am doing wrong?

[CELAMBARASAN]

Hi,

     Try this

Carton:

LOAD

[CAGE Region] as 'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Primary Brand] as 'Brand',

[Location Number] as 'Location_Number'

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

Carton_Bucket:

Load

Location_Number,

If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

Resident

     Carton

Group by Location_Number;

Celambarasan

[jagannalla]

Try like this it may helps u..

Temp:

LOAD

RowNo() as CartonEquivalent

AutoGenerate 100;

ResTemp:

LOAD CartonEquivalent as M1, '0-11' as Range

Resident Temp

Where CartonEquivalent <12;

Concatenate

LOAD CartonEquivalent as M1, '12-19' as Range

Resident Temp

Where CartonEquivalent <20

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '20-29' as Range

Resident Temp

Where CartonEquivalent <30

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '30-39' as Range

Resident Temp

Where CartonEquivalent <40

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '40-79' as Range

Resident Temp

Where CartonEquivalent <80

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '80+' as Range

Resident Temp

Where CartonEquivalent >=80;

Map1:

Mapping

LOAD M1,Range

Resident ResTemp;

DROP Table ResTemp;

Temp2:

LOAD ApplyMap ('Map1', CartonEquivalent ) as Range1,CartonEquivalent

Resident Temp;

DROP Table Temp;

[jagannalla]

If we convert according to your code. it will look like this

Temp:

LOAD

CartonEquivalent

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

ResTemp:

LOAD CartonEquivalent as M1, '0-11' as R1

Resident Temp

Where CartonEquivalent <12;

Concatenate

LOAD CartonEquivalent as M1, '12-19' as R1

Resident Temp

Where CartonEquivalent <20

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '20-29' as R1

Resident Temp

Where CartonEquivalent <30

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '30-39' as R1

Resident Temp

Where CartonEquivalent <40

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '40-79' as R1

Resident Temp

Where CartonEquivalent <80

and not Exists(M1,CartonEquivalent)

;

Concatenate

LOAD CartonEquivalent as M1, '80+' as R1

Resident Temp

Where CartonEquivalent >=80;

Map1:

Mapping

LOAD M1,R1

Resident ResTemp;

DROP Table ResTemp,Temp;

MainTable:

LOAD

[CAGE Region] as 'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Primary Brand] as 'Brand',

[Location Number] as 'Location_Number',

ApplyMap ('Map1', CartonEquivalent ) as Range

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);

[#5]

I got the following Script Error?

Table not found

Carton_Bucket:

LOAD

Location_Number,

If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

Resident

     Carton

Group by Location_Number

[#6]

I got the following Script Error?

Table not found

Carton_Bucket:

LOAD

Location_Number,

If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

Resident

     Carton

Group by Location_Number

[CELAMBARASAN]

Hi,

     Have you replaced the Carton_Bucket with my script(Carton and Carton_Bucket tables)

Celambarasan

[#8]

yes, I did and I got that error message.

I was thinking, can I accomplish this in a calculated dimension?  I would like to user to be able to click through the dimensions (brand, region, etc) and view the carton bucket in the graph.  Does that give more flexibility than doing it through the load script?

[#9]

yes, I did and I got that error message.

I was thinking, can I accomplish this in a calculated dimension?  I would like to user to be able to click through the dimensions (brand, region, etc) and view the carton bucket in the graph.  Does that give more flexibility than doing it through the load script?

[jagannalla]

Hello,

you should make group by[Carton Equivalent] and ur missing this field in main table.

Carton:

LOAD

[CAGE Region] as 'CAGE_Region',

[CAGE District] as 'CAGE_District',

[CAGE Territory] as 'CAGE_Territory',

MakeDate(Left([GL Date],4),Mid([GL Date],5,2),Right([GL Date],2)) as 'Calendar_Date',

[Primary Brand] as 'Brand',

[Location Number] as 'Location_Number',

[Carton Equivalent]

FROM

(txt, unicode, embedded labels, delimiter is '\t', msq);


Carton_Bucket:

Load

[Carton Equivalent],

If((Sum([Carton Equivalent])<12),'<12',If((Sum([Carton Equivalent])>=12 and Sum([Carton Equivalent])<20),'12-19',

If((Sum([Carton Equivalent])>=20 and Sum([Carton Equivalent])<30),'20-29',If((Sum([Carton Equivalent])>=30 and Sum([Carton Equivalent])<40),'30-39',

If((Sum([Carton Equivalent])>=40 and Sum([Carton Equivalent])<80),'40-79','80+'))))) as 'Carton_Bucket'

Resident

Carton

Group by [Carton Equivalent];

hope it helps u.