Transform Excel, part 2

[#1]

Hi,

I need to transform a large Excel-file (small part of it is attached for structure). I read the file to a QV app today, but need to add column Q as Total under Version. Total is the same value as Dec (Month).

Client need to use Version as part of a bar chart with Product as legend and 'Per. mål' (Goal), Utfall (Result) and Total as bars. (Three bars per product...)

Version has to be: 'Per. mål', Utfall and Total

Data for 'Per. mål' and Utfall is under Belopp (Amount) so data for Total has to be added to Belopp as well. Can you do this without rebuilding the entire Excel-file?

Scrip so far:

VD_Brev_Temp1:

CrossTable(Månad, Belopp, 4)
LOAD År,
      Segment,
      TRIM(Produkt) AS Produkt,
      Version,
      Jan,
      Feb,
      Mar,
      Apr,
      Maj,
      Jun,
      Jul,
      Aug,
      Sep,
      Okt,
      Nov,
      Dec
FROM $(Excel)Uppföljning VD-brev indata.xlsx
(ooxml, embedded labels, table is [Att fylla i])
WHERE Version <> 'Avvikelse';


VD_Brev:
LOAD
      År AS År_Temp,
      Månad AS Månad_Temp,
      År & '-' & If(Månad = 'Jan',01,
      If(Månad = 'Feb',02,
      If(Månad = 'Mar',03,
      If(Månad = 'Apr',04,
      If(Månad = 'Maj',05,
      If(Månad = 'Jun',06,
      If(Månad = 'Jul',07,
      If(Månad = 'Aug',08,
      If(Månad = 'Sep',09,
      If(Månad = 'Okt',10,
      If(Månad = 'Nov',11,
      If(Månad = 'Dec',12
      ,0)))))))))))) AS ÅrMånad,

      If(Månad = 'Jan',01,
      If(Månad = 'Feb',02,
      If(Månad = 'Mar',03,
      If(Månad = 'Apr',04,
      If(Månad = 'Maj',05,
      If(Månad = 'Jun',06,
      If(Månad = 'Jul',07,
      If(Månad = 'Aug',08,
      If(Månad = 'Sep',09,
      If(Månad = 'Okt',10,
      If(Månad = 'Nov',11,
      If(Månad = 'Dec',12
      ,0)))))))))))) AS MånadsNummer,
      Segment,
      Produkt,
      År & '_' & Segment &'_' & Trim(Produkt) as Key,
      Segment & ' - ' & Produkt AS Segment_Produkt,
      IF(Version <>  'Per. mål', 'Utfall', 'Per. mål') AS Version,
      Belopp,
      IF(Version <>  'Per. mål', Belopp, 0) AS Utfall,
      IF(Version =  'Per. mål', Belopp, 0) AS Mål

RESIDENT VD_Brev_Temp1;

DROP TABLE VD_Brev_Temp1;

Best regards

Torbjörn Ungvall (@Ungvall)

Senior Business Discovery Manager @ Advectas AB

[Miguel_Angel_Baeyens]

Hi Torbjörn,

Something like attached?

Hope that helps.

Miguel

EDIT: Version refined. Subtotal per month had the If() case insensitive "Per. mål" instead of "Per. Mål" according to the excel values therefore being all totals "Utfall".

[#3]

Thanks - Almost there!

They need to be able to select on month (Månad). In this case we have only a relation to Total when Dec is choosen or no month at all. Maybe one have to read the column Total instead and add that to Belopp (Amount). Total must be available no matter which month they choose...

Best regards

Torbjörn Ungvall (@Ungvall)

Senior Business Discovery Manager @ Advectas AB

[#4]

one more very usefull tip:

instead of using if() statments to convert month names to numbers,

use this:

num(month(date#(Month,'MMM')), '00')

dimension 'Month' was also month names like in your case (jan, feb,....)

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

This could be the option, doing that in both the script and the chart, but it will depend on the rest of the script and charts.

Hope that helps, though.

Miguel

[rohit214]

HI miguel,

I am using QV9 Sr7 what i am doing is exporting a pivot table by using macro but what is happening

its converts that excel sheet to into ooxml formate i want that expoted file in biff format but whenn i am exporting that pivot table manually at that time that feile format is in biff

so please help me to convert or generate biff file

Thanks Regards

rohit

[Miguel_Angel_Baeyens]

Hi,

The export will use the operating system libraries, meaning that if you have installed, for example, Office 2003, the result will be .XLS (BIFF) files. But if you have installed Office 2007 or 2010 the resulting file will be .XLSX (OOXML) by default.

Are you just right clicking and selecting export or using a macro or an action?

Miguel