Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have excel file with attached formate.I have to load that file into following imag formate..
Can any one explain me step by step..
Thanks,
I would load the data first assuming that columns are fixed (2 columns per month, months in order) and then crosstable the result to make it more like a fact table.
see attached
Hi,
When loading the data from excel sheet use under labels-> explicit labels
after that under Header size-> Lines-> change that 0 to 1 -> than click on finish u can see the o/p as
LOAD id,
Name,
No,
[project value],
efforts,
[project value1],
efforts1
FROM
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
after that according to u r requirement you can change like this
LOAD id,
Name,
No,
[project value] as [Jan project value ],
efforts as [Jan efforts ],
[project value1] as [Feb project value1] ,
efforts1 as [Feb efforts1]
FROM
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
with the help of straight table you can see u r o/p
thank you,
balaji.k
Step-by-step?
First copy this piece of code into your load script:
LET vStartDate = '1/1/2015';
RawData:
CROSSTABLE(FName, Data, 3)
LOAD * FROM [sample.xlsx](ooxml, embedded labels, header is 1 lines, table is Sheet1);
FinalTable:
NOCONCATENATE
LOAD id, Name, No,
dual
( Date(AddMonths('$(vStartDate)',alt(num#(KeepChar(FName,'1234567890')),0)),'MMM ')
& PurgeChar(FName, '1234567890'),
Date(AddMonths('$(vStartDate)',alt(num#(KeepChar(FName,'1234567890')),0)),'MMM ')*2
+ IF (left(FName, 3) = 'eff', 1, 0)
) AS Label,
Data
RESIDENT RawData;
DROP Table RawData;
Note: this will pick up any additional columns you may add in the future.
Next, Reload
Then, create a pivot with dimensions id, Name, No, and Label. Add a single expression =Sum(Data) Do not forget to add =' ' (a single space) as a custom label to dimension Label.
Finally, pivot the dimension Label so that it becomes a horizontal dimension above the expression.
You'll get something like:
Doc in attachment.
Best,
Peter