Excel Import Issue

[jasonwills]

Hi All,

I am hoping someone might be able to guide me.

I have an issue where I cannot really adjust the excel sheet to aid in the import.

Excel is formatted as in the attached dates.xlsx, however, I need to be able to say that each month is for a certain year, until the year changes - hopefully makes sense when seeing the excel sheet. (all figures are random)

Thanks for any guidance.

Regards

Jason

[tamilarasu]

Jason,

You can try any one of the below methods.

By using Enable Transformation Method:

Data:
LOAD Num(F1) as Year, 
     F2 as Country, 
     F3 as Month,
    // Num(F1) & Date(Date#(F3,'MMM'),'MM') as YearMonth, 
     Num(revenue) as revenue, 
     Num(cost) as Cost
FROM
dates.xlsx
(ooxml, embedded labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
)) where F3 <> 'TOTAL';

By using Peek function:

Temp:
LOAD Recno() as RecNo,
     F1 as Year,
     F2 as Country, 
     Date(Date#(F3,'MMM'),'MMM') as Month, 
     revenue, 
     cost
FROM
dates.xlsx
(ooxml, embedded labels, table is Sheet1)
 where F3 <> 'TOTAL';

NoConcatenate
Data:
LOAD RecNo,
     If(Len(Trim(Year))=0,Peek('Year'),Year) as Year,
     If(Len(Trim(Country))=0,Peek('Country'),Country) as Country,
     Month, 
     revenue, 
     cost
Resident Temp Order by RecNo; 

 DROP Table Temp;  

Attached qvw file for your sample date. Let me know.

Note: I have excluded the 'TOTAL' Rows.