topic Re: Cross Table in QlikView

Cross Table

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I'm trying to do a cross table in Qlik but it's not producing the desired results. I want something like this but my QVW isn't working out for

some odd reason. If anyone can provide insights please do.

Thanks,

Re: Cross Table

Fri, 01 Apr 2016 14:58:16 GMT

Forgot to attach the QVW.

Re: Cross Table

Kushal_Chawda — Fri, 01 Apr 2016 15:03:26 GMT

try this

Cohort:

CrossTable (Months, DATA,5)

LOAD [Customer Internal ID],

Customer,

Average,

[% of Total],

Category,

[2015 09],

[2015 10],

[2015 11],

[2015 12],

[2016 01],

[2016 02]

FROM

[Churn Summary - 2015 03 30 Latest.xlsx]

(ooxml, embedded labels, table is [MRR by Customer (2)]);

CohortTable:

NoConcatenate

LOAD [Customer Internal ID],

Month(Date#(Months, 'MMM')) as Months,

DATA

Resident Cohort;

DROP Table Cohort;

Re: Cross Table

Fri, 01 Apr 2016 15:08:47 GMT

No the months column still empty. The problem is [2015 09] to [2016 02] have numbers stored within them I can't use them as dimensions and then expressions for both Cross Table is the only tactic I can think of. Months as Dimensions and their sum as expression.

Re: Cross Table

Kushal_Chawda — Fri, 01 Apr 2016 15:28:40 GMT

Can you share the excel file?

Re: Cross Table

Fri, 01 Apr 2016 15:32:58 GMT

Please find attached the document.

Re: Cross Table

Fri, 01 Apr 2016 16:06:50 GMT

Can someone please provide any insights on this issue?

Re: Cross Table

Kushal_Chawda — Fri, 01 Apr 2016 16:26:31 GMT

Data:
CrossTable(Month,Data,3)
LOAD [Customer Internal ID],
Average,
[% of Total],
[2015 09],
[2015 10],
[2015 11],
[2015 12],
[2016 01],
[2016 02]
FROM
[STK.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Sheet1);

New:
LOAD monthname(date#(Month,'YYYY MM')) as MonthYear,
[Customer Internal ID],
Data
Resident Data
where [Customer Internal ID]<>'#N/A' and not IsNull([Customer Internal ID]);

DROP Table Data;

Re: Cross Table

tamilarasu — Fri, 01 Apr 2016 17:13:57 GMT

Hi Aaqureshi,

Here is the dynamic code which will load all the columns.

Temp:
CrossTable([YearMonth], Data)
LOAD * FROM
STK.xlsx
(ooxml, embedded labels, header is 2 lines, table is Sheet1);

NoConcatenate
LOAD [Customer Internal ID],
Left(YearMonth,4) as Year,
Month(Date#(Right(YearMonth,2),'MM')) as Month,
YearMonth,
Data
Resident Temp where Not Match(YearMonth,'Average','% of Total') and [Customer Internal ID] <>'#N/A';
Drop Table Temp;

Re: Cross Table

Fri, 01 Apr 2016 18:06:06 GMT

Thanks guys for all your help.

Re: Cross Table

Fri, 01 Apr 2016 20:35:23 GMT

Hi Kush,

How were you able to hide the null values in the table box?

Please provide assistance!!!!

Re: Cross Table

Fri, 01 Apr 2016 20:56:38 GMT

Nevermind I got it.

Thanks

Re: Cross Table

Kushal_Chawda — Sat, 02 Apr 2016 05:52:34 GMT

awsome