topic Re: Cross Table Load in QlikView

Cross Table Load

Tue, 29 Dec 2015 03:33:44 GMT

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

Re: Cross Table Load

oknotsen — Tue, 29 Dec 2015 10:27:48 GMT

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

Re: Cross Table Load

arulsettu — Tue, 29 Dec 2015 10:31:40 GMT

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

Re: Cross Table Load

prma7799 — Tue, 29 Dec 2015 10:36:20 GMT

Hi Jack,

PFA....

Re: Cross Table Load

prma7799 — Tue, 29 Dec 2015 10:36:46 GMT

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

Re: Cross Table Load

MarcoWedel — Tue, 29 Dec 2015 16:10:14 GMT

Hi,

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

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

Re: Cross Table Load

Wed, 30 Dec 2015 07:37:28 GMT

Hi Marco,

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

Thank You

Re: Cross Table Load

MarcoWedel — Wed, 30 Dec 2015 09:43:30 GMT

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

Re: Cross Table Load

Wed, 30 Dec 2015 09:51:38 GMT

Thank You Marco

Re: Cross Table Load

Wed, 30 Dec 2015 09:53:35 GMT

Marco,

How to create Master Calendar for this Data?

Thanks in Advance

Re: Cross Table Load

oknotsen — Wed, 30 Dec 2015 10:03:42 GMT

Please flag the correct answer to your initial question as the Correct Answer and start a new topic for your new question.

Thank you.

Re: Cross Table Load

jagan — Thu, 07 Jan 2016 08:24:36 GMT

Hi,

Check this link for more details on crosstable()

The Crosstable Load

Regards,

Jagan.