Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I need a Qtr to be built from the attached excel in QV.. Nov,Dec,Jan-to be Q1...Feb,Mar,Apr-Q2 ...so on...
I have created a cross table with the attached data and in my Script it looks like below..
Plz Help!
Use below script,
Temp:
CrossTable(Month, Units, 7)
LOAD [Platform Capacity],
[Capacity Group],
[Capacity Group Base Cap],
Version,
[Product Platform],
Region,
KF,
Jan'18,
Feb'18,
Mar'18,
Apr'18,
May'18,
Jun'18,
Jul'18,
Aug'18,
Sep'18,
Oct'18
FROM
pivotExcel.xlsx
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Final:
Load *,
'Q ' & if(ceil(Month(MonthYear)/3)=1,4,ceil(Month(MonthYear)/3)-1) as fis_quarter,
'Q ' & ceil(Month(MonthYear)/3) as quarter,
'Q ' & if(wildmatch(Month(MonthYear),'Nov','Dec','Jan'),1,
if(wildmatch(Month(MonthYear),'Feb','Mar','Apr'),2,
if(wildmatch(Month(MonthYear),'May','Jun','Jul'),3,
if(wildmatch(Month(MonthYear),'Aug','Sept','Oct'),4,
)))) as custom_Quarter
;
Load *,Date(Date#(Month,'MMM'&chr(39)&'YY')) as MonthYear Resident Temp;
drop table Temp;
Regards,
PFA,
may be helpful....
Use below script,
Temp:
CrossTable(Month, Units, 7)
LOAD [Platform Capacity],
[Capacity Group],
[Capacity Group Base Cap],
Version,
[Product Platform],
Region,
KF,
Jan'18,
Feb'18,
Mar'18,
Apr'18,
May'18,
Jun'18,
Jul'18,
Aug'18,
Sep'18,
Oct'18
FROM
pivotExcel.xlsx
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Final:
Load *,
'Q ' & if(ceil(Month(MonthYear)/3)=1,4,ceil(Month(MonthYear)/3)-1) as fis_quarter,
'Q ' & ceil(Month(MonthYear)/3) as quarter,
'Q ' & if(wildmatch(Month(MonthYear),'Nov','Dec','Jan'),1,
if(wildmatch(Month(MonthYear),'Feb','Mar','Apr'),2,
if(wildmatch(Month(MonthYear),'May','Jun','Jul'),3,
if(wildmatch(Month(MonthYear),'Aug','Sept','Oct'),4,
)))) as custom_Quarter
;
Load *,Date(Date#(Month,'MMM'&chr(39)&'YY')) as MonthYear Resident Temp;
drop table Temp;
Regards,
TY Raju!!
TY Prashanth!! Worked perfectly Fine!!
Hello, Raju
Now I have Parameters with Blanks, like this:
What could I do to delete Dates with no Data?
Regards