Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, to all,
Until yesterday this option was working fine, but when I changed my Cross Table to concatenate two field (T$PERI$O) this mean Month, then it not working anymore (see attached file that explain and show the setup option, I´ll appreciate your help to find the mistake.
Tanks in advance,
Hi,
First question is: are both tables loading months from same year? The one you are showing is using MakeDate() with year 2011. Has the concatenated table values from months from year 2011 as well?
BI Consultant
Hi Miguel, Thanks for, you quick response
Yes, Both Tables loading months from same year, the firts one is a Excell (Budget )and second one is my DB Oracle (Sales).
Yes, I need to concateate it because the Month is a Synckey.
I would like to compare Budget vs. real Sales from current year until tow months ago.
Hi,
Are you marking in the concatenation the source of the records (Budget and Sales)? If you don't, and if the amount field name is the same for both budget and sales, you cannot distiguish between both.
So the concatenation should look like this
Sales:
LOAD Date,
Amount,
'Sales' AS Source
FROM SalesSource;
Budget:
LOAD Date,
Amount,
'Budget' AS Source
FROM BudgetSource;
Then your expression to get the budget can be
Sum({< Source = {'Budget'} >} Amount)
And for sales
Sum({< Source = {'Sales'} >} Amount)
Anyway, the complete script and some sample data would help, I'm afraid I cannot understand the document you attached.
Hope that helps.
BI Consultant
Scrip
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='Bs #.##0,00;Bs -#.##0,00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
SET MonthNames='Ene;Feb;Mar;Abr;May;Jun;Jul;Ago;Sep;Oct;Nov;Dic';
SET DayNames='Lun;Mar;Mié;Jue;Vie;Sáb;Dom';
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=baan;Data Source=baan_10.72.102.100;Extended Properties=""] (XPassword is EbOWBRJOPDdQWSJOTTYCWQZNVF);
SQL SELECT * (HERE is MY LES INFORMATION)
FROM BAAN.TTDSLS824804; (Where T$PERI$O mean MONTH & T$DQUA$O Mean Sales Amount)
SQL SELECT "T$COPR",
"T$ITEM" "T$ITEM$O",
"T$CWAR",
"T$MATC",
"T$OPRC"
FROM BAAN.TTIITM001804;
SQL SELECT "T$ITEM" "T$ITEM$O",
"T$CWAR",
"T$STOC",
"T$ORDR",
"T$ALLO"
FROM BAAN.TTDINV001804;
SQL SELECT "T$ITEM" "T$ITEM$O",
"T$ORNO" "T$ORNO$O",
"T$PONO" "T$PONO$O",
"T$CUNO" "T$CUNO$O",
"T$OQUA",
"T$BQUA"
FROM BAAN.TTDSLS041804;
LOAD * INLINE [
T$PERI$O, Name
1, Ene
2, Feb
3, Mar
4, Abr
5, May
6, Jun
7, Jul
8, Ago
9, Sep
10. Oct
11, Nov
12, Dic
];
Step1:
crosstable(T$PERI$O, Cantidad, 4) (T$PERI$O mean MONTH)
LOAD
T$YEAR$O,
T$CITG$O,
[Desc Familia],
T$ITEM$O,
[1] AS 1,
[2] As 2,
[3] AS 3,
[4] AS 4,
[5] As 5,
[6] AS 6,
[7] AS 7,
[8] AS 8,
[9] AS 9,
[10] AS 10,
[11] AS 11,
[12] AS 12
FROM
(biff, embedded labels, table is [data unidades y Dolares$]);
Step2:
NOCONCATENATE LOAD T$YEAR$O, T$CITG$O,[Desc Familia],T$ITEM$O,
Month(MakeDate(2011,Num(Num#(T$PERI$O)))) AS T$PERI$O,
Cantidad (Cantidad mean Budget AMOUNT)
DROP TABLE Step1;
BUDGET EXCEL FILE Example
T$YEAR$O | T$CITG$O | Desc Familia | T$ITEM$O | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2011 | PT7020 | Air Passenger(Air,Cabin,Panel) | I-46340 | 3 | 0 | 15 | 1 | 1 | 15 | 1 | 2 | 15 | 1 | 1 | 3 |
2011 | PT7020 | Air Passenger(Air,Cabin,Panel) | I-47040 | 19 | 64 | 96 | 22 | 13 | 96 | 13 | 19 | 96 | 13 | 13 | 16 |
2011 | PT7020 | Air Passenger(Air,Cabin,Panel) | I-42159 | 2 | 23 | 5 | 0 | 7 | 5 | 2 | 0 | 5 | 2 | 7 | 0 |
2011 | PT7020 | Air Passenger(Air,Cabin,Panel) | I-42524 | 0 | 30 | 14 | 3 | 9 | 14 | 0 | 38 | 14 | 0 | 9 | 12 |
2011 | PT7020 | Air Passenger(Air,Cabin,Panel) | I-46411 | 1 | 1 | 4 | 1 | 2 | 4 | 2 | 2 | 4 | 2 | 2 | 1 |
2011 | PT7020 | Air Passenger(Air,Cabin,Panel) | I-42330 | 3 | 0 | 1 | 48 | 1 | 1 | 6 | 1 | 1 | 6 | 1 | 0 |