Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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;
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);
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
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
Hi,
Have you replaced the Carton_Bucket with my script(Carton and Carton_Bucket tables)
Celambarasan
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?
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?
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.