Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table as below
Report | Area | Map | groceries PV | groceries AC | Electric PV | Electric AC | mail PV | mail AC |
05_2016 | DE | 05_2016_DE | 45 | 43 | 50 | 56 | 30 | 43 |
06_2016 | ES | 06_2016_ES | 67 | 56 | 51 | 78 | 31 | 23 |
07_2016 | FR | 07_2016_FR | 78 | 78 | 52 | 87 | 32 | 33 |
08_2016 | BA | 08_2016_BA | 89 | 90 | 53 | 65 | 33 | 43 |
Based on the above table I would like to convert it into table as below:
MapAttributePVAC
05_2016_DE | groceries | 45 | 43 |
05_2016_DE | 30 | 43 | |
05_2016_DE | Electric | 50 | 56 |
06_2016_ES | groceries | 67 | 56 |
06_2016_ES | 31 | 23 | |
06_2016_ES | Electric | 51 | 78 |
07_2016_FR | groceries | 78 | 78 |
07_2016_FR | 32 | 33 | |
07_2016_FR | Electric | 52 | 87 |
08_2016_BA | groceries | 89 | 90 |
08_2016_BA | 33 | 43 | |
08_2016_BA | Electric | 53 | 65 |
note: the result table is not shown completely
To do so I wrote logic as below in Data load editor and I am stuck with no output in the data model viewwe . Could some one please look into my code ? Or if you have any better solution to do the above process it will be great help:
the code is as follows:
List:
LOAD
Report,
Area,
"Map",
"groceries PV",
"groceries AC",
"Electric PV",
"Electric AC",
"mail PV",
"mail AC"
FROM [lib://AttachedFiles/Dummu.xlsx]
(ooxml, embedded labels, table is test);
PV:
load
Map,
sum("groceries PV") as GRS_PV,
sum("Electric PV") as ELE_PV,
sum("mail PV") as MA_PV,
sum("groceries PV") +sum("Electric PV") +sum("mail PV") as Total_PV
Resident List
group by Map;
PVUnpivot:
CROSSTABLE (ID,TypePV,1)
Load
Map,
GRS_PV,
ELE_PV,
MA_PV,
Total_PV
RESIDENT PV;
drop Table PV;
AC:
load
Map,
sum("groceries AC") as GRS_AC,
sum("Electric AC") as ELE_AC,
sum("mail AC") as MA_AC,
sum("groceries AC") +sum("Electric AC") +sum("mail AC") as Total_AC
Resident List
group by Map;
ACUnpivot:
CROSSTABLE (ID,TypeAC,1)
Load
Map,
GRS_AC,
ELE_AC,
MA_AC,
Total_AC
RESIDENT AC;
drop Table AC;
Costs:
load
*
Resident PVUnpivot;
outer Join load * Resident ACUnpivot;
drop table PVUnpivot;
drop table ACUnpivot;
Regards and thanks
Iras
I found the solution.. Thanks alot guys
I found the solution.. Thanks alot guys