Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bharani8
Creator III
Creator III

Creating Qtr View in Cross table

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..

CrossTable(Zenith_Month, Zenith_Units, 7)

LOAD [Platform Capacity],

     [Capacity Group] as Zenith_Capacity_Grp,

     [Capacity Group Base Cap],

     Version,

     [Product Platform],

     Region as Zenith_Region,

     KF,

     [Jan'18],

     [Feb'18],

     [Mar'18],

     [Apr'18],

     [May'18],

     [Jun'18],

     [Jul'18],

     [Aug'18],

     [Sep'18],

     [Oct'18]

     

FROM

(ooxml, embedded labels, table is Sheet1);

Plz Help!

stalwar1anilbabuQlikView Forums & Resources

1 Solution

Accepted Solutions
PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

5 Replies
qv_testing
Specialist II
Specialist II

PFA,

may be helpful....

PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

bharani8
Creator III
Creator III
Author

TY Raju!!

bharani8
Creator III
Creator III
Author

TY Prashanth!!  Worked perfectly Fine!!

filippo_cardoso
Contributor III
Contributor III

Hello, Raju

Now I have Parameters with Blanks, like this:

What could I do to delete Dates with no Data?

Regards