topic Re: Creating Qtr View in Cross table in QlikView

Creating Qtr View in Cross table

bharani8 — Wed, 25 Nov 2020 16:16:04 GMT

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!

stalwar1‌ anilbabu‌ QlikView Forums & Resources‌

Re: Creating Qtr View in Cross table

Nagaraju_KCS — Fri, 30 Mar 2018 04:47:24 GMT

PFA,

may be helpful....

Re: Creating Qtr View in Cross table

PrashantSangle — Fri, 30 Mar 2018 04:58:27 GMT

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,

Re: Creating Qtr View in Cross table

bharani8 — Fri, 30 Mar 2018 06:40:58 GMT

TY Raju!!

Re: Creating Qtr View in Cross table

bharani8 — Fri, 30 Mar 2018 06:41:31 GMT

TY Prashanth!! Worked perfectly Fine!!

Re: Creating Qtr View in Cross table

Anonymous — Tue, 03 Apr 2018 14:58:17 GMT

Hello, Raju

Now I have Parameters with Blanks, like this:

What could I do to delete Dates with no Data?

Regards