Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Cross Table Load

Hi Experts,

I have the data which consists of Monthly wise and Year total. I want to load it as Cross table. How to achieve this?

Please find attached sheet. I am using Personal Edition please paste the script

Thanks in Advance

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Cross Table Load

Hi,

to get a more generic script that also applies to other files, you could use "load *" instead of hard coding the field names:

QlikCommunity_Thread_197837_Pic1.JPG

QlikCommunity_Thread_197837_Pic2.JPG

tabProjects:

CrossTable ([Project Period],Value, 3)

LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/941366-203669/Sample.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

View solution in original post

11 Replies
Highlighted
Master III
Master III

Re: Cross Table Load

Here you go.

Note: For the sake of saving space I skipped most of the columns, but if you just do a regular load of the xls and put that "crosstable" code row above it, it should work.

Good luck!

SomeTable:
CrossTable(Dates, Totals, 3)
LOAD Status,
[Start Date],
[End Date],
[Proj A Jan 16],
[Proj A Feb 16],
[Proj A Mar 16],
[Proj A Apr 16],
[Proj A May 16],
[Proj A Jun 16],
[Proj A Jul 16],
[Proj A Aug 16],
[Proj A Sep 16],
[Proj A Oct 16],
[Proj A Nov 16]
FROM
Sample.xlsx
(
ooxml, embedded labels, table is Sheet1);

Don't allow Perfection to be the enemy of Good Enough.
May you live in interesting times!
Highlighted
Master III
Master III

Re: Cross Table Load

may be this

CrossTable([Start Date], Data)

LOAD Status,

     [Start Date],

     [End Date],

     [Proj A Jan 16],

     [Proj A Feb 16],

     [Proj A Mar 16],

     [Proj A Apr 16],

     [Proj A May 16],

     [Proj A Jun 16],

     [Proj A Jul 16],

     [Proj A Aug 16],

     [Proj A Sep 16],

     [Proj A Oct 16],

     [Proj A Nov 16],

     [Proj A Dec 16],

     [Proj A 2016],

     [Proj A 2017],

     [Proj A 2018],

     [Proj B Jan 16],

     [Proj B Feb 16],

     [Proj B Mar 16],

     [Proj B Apr 16],

     [Proj B May 16],

     [Proj B Jun 16],

     [Proj B Jul 16],

     [Proj B Aug 16],

     [Proj B Sep 16],

     [Proj B Oct 16],

     [Proj B Nov 16],

     [Proj B Dec 16],

     [Proj B 2016],

     [Proj B 2017],

     [Proj B 2018],

     [Proj C Jan 16],

     [Proj C Feb 16],

     [Proj C Mar 16],

     [Proj C Apr 16],

     [Proj C May 16],

     [Proj C Jun 16],

     [Proj C Jul 16],

     [Proj C Aug 16],

     [Proj C Sep 16],

     [Proj C Oct 16],

     [Proj C Nov 16],

     [Proj C Dec 16],

     [Proj C 2016],

     [Proj C 2017],

     [Proj C 2018],

     [Proj D  Jan 16],

     [Proj D  Feb 16],

     [Proj D  Mar 16],

     [Proj D  Apr 16],

     [Proj D  May 16],

     [Proj D  Jun 16],

     [Proj D  Jul 16],

     [Proj D  Aug 16],

     [Proj D  Sep 16],

     [Proj D  Oct 16],

     [Proj D  Nov 16],

     [Proj D  Dec 16],

     [Proj D  2016],

     [Proj D  2017],

     [Proj D  2018],

     [Proj E 2016 (Proj B + Proj C + Proj D)],

     [Proj E 2017 (Proj B + Proj C + Proj D)],

     [Proj E 2018 (Proj B + Proj C + Proj D)],

     [Proj F 2016 (Proj A + Proj B)],

     [Proj F 2017 (Proj A + Proj B)],

     [Proj F 2018 (Proj A + Proj B)],

     F70

FROM

(ooxml, embedded labels, table is Sheet1);

Highlighted
Master III
Master III

Re: Cross Table Load

Hi Jack,

PFA....

Highlighted
Master III
Master III

Re: Cross Table Load

Cross:

CrossTable(Date, Totals, 3)

LOAD Status,

     [Start Date],

     [End Date],

     [Proj A Jan 16],

     [Proj A Feb 16],

     [Proj A Mar 16],

     [Proj A Apr 16],

     [Proj A May 16],

     [Proj A Jun 16],

     [Proj A Jul 16],

     [Proj A Aug 16],

     [Proj A Sep 16],

     [Proj A Oct 16],

     [Proj A Nov 16],

     [Proj A Dec 16],

     [Proj A 2016],

     [Proj A 2017],

     [Proj A 2018],

     [Proj B Jan 16],

     [Proj B Feb 16],

     [Proj B Mar 16],

     [Proj B Apr 16],

     [Proj B May 16],

     [Proj B Jun 16],

     [Proj B Jul 16],

     [Proj B Aug 16],

     [Proj B Sep 16],

     [Proj B Oct 16],

     [Proj B Nov 16],

     [Proj B Dec 16],

     [Proj B 2016],

     [Proj B 2017],

     [Proj B 2018],

     [Proj C Jan 16],

     [Proj C Feb 16],

     [Proj C Mar 16],

     [Proj C Apr 16],

     [Proj C May 16],

     [Proj C Jun 16],

     [Proj C Jul 16],

     [Proj C Aug 16],

     [Proj C Sep 16],

     [Proj C Oct 16],

     [Proj C Nov 16],

     [Proj C Dec 16],

     [Proj C 2016],

     [Proj C 2017],

     [Proj C 2018],

     [Proj D  Jan 16],

     [Proj D  Feb 16],

     [Proj D  Mar 16],

     [Proj D  Apr 16],

     [Proj D  May 16],

     [Proj D  Jun 16],

     [Proj D  Jul 16],

     [Proj D  Aug 16],

     [Proj D  Sep 16],

     [Proj D  Oct 16],

     [Proj D  Nov 16],

     [Proj D  Dec 16],

     [Proj D  2016],

     [Proj D  2017],

     [Proj D  2018],

     [Proj E 2016 (Proj B + Proj C + Proj D)],

     [Proj E 2017 (Proj B + Proj C + Proj D)],

     [Proj E 2018 (Proj B + Proj C + Proj D)],

     [Proj F 2016 (Proj A + Proj B)],

     [Proj F 2017 (Proj A + Proj B)],

     [Proj F 2018 (Proj A + Proj B)],

     F70

FROM

(ooxml, embedded labels, table is Sheet1);

Highlighted

Re: Cross Table Load

Hi,

to get a more generic script that also applies to other files, you could use "load *" instead of hard coding the field names:

QlikCommunity_Thread_197837_Pic1.JPG

QlikCommunity_Thread_197837_Pic2.JPG

tabProjects:

CrossTable ([Project Period],Value, 3)

LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/941366-203669/Sample.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

View solution in original post

Highlighted
Not applicable

Re: Cross Table Load

Hi Marco,

" CrossTable ([Project Period],Value, 3) " in this 3 refers to?



Thank You

Highlighted

Re: Cross Table Load

Hi,

from help:

The syntax is:

crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )

where:

attribute field name is the field to contain the attribute values.

data field name is the field to contain the data values.

n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.


hope this helps


regards


Marco

Highlighted
Not applicable

Re: Cross Table Load

Thank You Marco

Highlighted
Not applicable

Re: Cross Table Load

Marco,

How to create Master Calendar for this Data?

Thanks in Advance