Crosstable of crosstable/ crosstable with multiple criteria

[#1]

Category	Product	Fou	Key	Jul'14	Aug'14	Sep'14	Oct'14	Nov'14	Dec'14	Jan'15	Feb'15	Mar'15	Apr'15	May'15	Jun'15	Jul'15	Aug'15	Sep'15	Oct'15	Nov'15	Dec'15
A	DIN	GFT	GFDINOFT Yld1111	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%
B	DIN	GFT	GFDINOGDPW1111	1,279	1,295	1,310	1,316	1,317	1,317	1,345	1,345	1,345	1,363	1,363	1,363	1,382	1,382	1,382	1,382	1,382	1,382

I am trying to transform above table into

Category| Product | Fou | Key | Month | data

A            | DIN      | GFT | GFD---| Sept'14| 98.5

B            | DIN      | GFT | GFD---|Sept '14| 1310

and so on. I used below code and could do it

Directory;

CrossTable(Month, Data, 4)

LOAD Category,

     Product,

     Foundry,

     Key,

     [Jul'14],

     [Aug'14],

     [Sep'14],

     [Oct'14],

     [Nov'14],

     [Dec'14],

     [Jan'15],

     [Feb'15],

     [Mar'15],

     [Apr'15],

     [May'15],

     [Jun'15],

     [Jul'15],

     [Aug'15],

     [Sep'15],

     [Oct'15],

     [Nov'15],

     [Dec'15]

FROM

[FRODO_BAGH_AV_DINO_WIP_flush_9_3_14 (4).xlsx]

(ooxml, embedded labels, table is YIELD);

Now I want to chnage it to

Product | Fou | Key    | Month   | A     | B

DIN      | GFT | GFD---| Sept'14| 98.5 | 1310

How do I do it?

[JonnyPoole]

Here is a load script that will restructure the table:

temp:

CrossTable(Month, Values, 4)

LOAD Category,

     Product,

     Fou,

     Key,

     [Jul'14],

     [Aug'14],

     [Sep'14],

     [Oct'14],

     [Nov'14],

     [Dec'14],

     [Jan'15],

     [Feb'15],

     [Mar'15],

     [Apr'15],

     [May'15],

     [Jun'15],

     [Jul'15],

     [Aug'15],

     [Sep'15],

     [Oct'15],

     [Nov'15],

     [Dec'15]

FROM

[http://community.qlik.com/thread/136624]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

Data:

Load

  Product,

  Month,

  Fou,

  Values as A,

  left(Key,3) & '---' as Key

resident temp

Where Category='A';

left join (Data)

load

  Product,

  Month,

  Fou,

  Values as B,

  left(Key,3) & '---' as Key

Resident temp

where Category='B';

drop table temp;

[JonnyPoole]

And the following will be more generic, with no hard coding of category names (Same chart result).

The Crosstable brings months into a vertical list and the generic load expands category and values into as many fields as there are unique category values.

-----------

temp:

CrossTable(Month, Values, 4)

LOAD Category,

     Product,

     Fou,

     Key,

     [Jul'14],

     [Aug'14],

     [Sep'14],

     [Oct'14],

     [Nov'14],

     [Dec'14],

     [Jan'15],

     [Feb'15],

     [Mar'15],

     [Apr'15],

     [May'15],

     [Jun'15],

     [Jul'15],

     [Aug'15],

     [Sep'15],

     [Oct'15],

     [Nov'15],

     [Dec'15]

FROM

[http://community.qlik.com/thread/136624]

(html, codepage is 1252, embedded labels, table is @1);

Data:

Generic load

  Product,

  Month,

  Fou,

  left(Key,3) & '---' as Key,

  Category,

  Values

Resident temp;

drop table temp;

[MarcoWedel]

Hi,

one solution might be also:

tabInput:
CrossTable (MonthText,data,4)
LOAD *
FROM [http://community.qlik.com/thread/136624] (html, codepage is 1252, embedded labels, table is @1);

Left Join (tabInput)
LOAD Distinct
  MonthText,
  Date#(MonthText, 'MMM'&chr(39)&'YY') as Month
Resident tabInput;

Left Join (tabInput)
LOAD Distinct
  Key,
  Left(Key,3)&'---' as KeyTemp
Resident tabInput;

DROP Fields MonthText, Key;

RENAME Field KeyTemp to Key;

tabOutput:
Generic LOAD
  Product,
  Fou,
  Key,
  Month,
  Category,
  data
Resident tabInput;

DROP Table tabInput;

I tried to create a solution that doesn't include hard coded column names in order to work for input tables with different month columns as well. This solution also creates a real date field "Month" to be able to sort and calculate by/with this field.

hope this helps

regards

Marco

[#5]

Hi Wedel,

Thanks for helping me!

I am pretty new to this forum and qlikview.

I tried to add in your code to my app. But got confused.

Attaching the exact app

and the data

Category	Product	Foundry	Key	Jul'14	Aug'14	Sep'14	Oct'14	Nov'14	Dec'14	Jan'15	Feb'15	Mar'15	Apr'15	May'15	Jun'15	Jul'15	Aug'15	Sep'15	Oct'15	Nov'15	Dec'15
FT Yld	DINO	GF	GFDINOFT Yld	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%	98.5%
GDPW	DINO	GF	GFDINOGDPW	1,279	1,295	1,310	1,316	1,317	1,317	1,345	1,345	1,345	1,363	1,363	1,363	1,382	1,382	1,382	1,382	1,382	1,382

[MarcoWedel]

Hi,

does the attached example work with your input files?

regards

Marco

[#7]

No. It doesnt

[MarcoWedel · Accepted solution]

combining Jonathan's and my solution could lead to a even shorter yet generic solution:

tabInput:
CrossTable (Month,data,4)
LOAD *
FROM [http://community.qlik.com/thread/136624] (html, codepage is 1252, embedded labels, table is @1);

tabOutput:
Generic LOAD
  Product,
  Fou,
  Left(Key,3)&'---' as Key,
  Date#(Month, 'MMM'&chr(39)&'YY') as Month,
  Category,
  data
Resident tabInput;

DROP Table tabInput;

hope this helps

regards

Marco