Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.