Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
bharani8
Creator III
Creator III
Author

TY Raju!!

bharani8
Creator III
Creator III
Author

TY Prashanth!!  Worked perfectly Fine!!

Anonymous
Not applicable

Hello, Raju

Now I have Parameters with Blanks, like this:

What could I do to delete Dates with no Data?

Regards