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

1 Solution

Accepted Solutions
MarcoWedel

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
oknotsen
Master III
Master III

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);

May you live in interesting times!
arulsettu
Master III
Master III

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);

prma7799
Master III
Master III

Hi Jack,

PFA....

prma7799
Master III
Master III

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);

MarcoWedel

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

Not applicable
Author

Hi Marco,

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



Thank You

MarcoWedel

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

Not applicable
Author

Thank You Marco

Not applicable
Author

Marco,

How to create Master Calendar for this Data?

Thanks in Advance