Crosstable after Removing first row

[Pratik81]

Hi, 

I am New to Qlik and wanted some help in regards to applying a cross table after loading selected data from an excel file. 

The columns selected in the spreadsheet are consistent in report hence i have only loaded the range of data required within the sheet. 

However since i have selected no lables when loading the data is there anyway to promote the headers and apply the crosstable ?

The result i am looking for is metric as the qualifer, monthyear as attribute and then data field. 

Thanks in advance

table:

Load

"A",
"E",
"F",
"G",
"H",
"I",
"J",
"K",
"L",
"M",
"N",
"O",
"P"

FROM [Table.xlsx]
(ooxml, no labels, header is 1 lines, table is Sheet)
where RecNo() >11
and RecNo()< 38;

[LRuCelver]

table:
Load
    ... Field Names here ...
FROM [Table.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet)
where RecNo() >11
and RecNo()< 38;

[Pratik81]

Thanks for the reply. Sorry forgot to mention. The issue here is that the dates are rolling 12 months hence will be dynamic as the headers will change every month; as a new month gets added to column P and everything gets shifted back by a month in the rest of the columns apart from column A on the source file.

Hence i used the no lables to only load those columns which represent the 12 months.

Thanks 

[LRuCelver]

Do I understand correctly that the newest month is always added to column P in the source file and all previous months are shifted to the left? The earliest month in column B is discarded?

Is there any more data in the file in columns right of column P?

[Pratik81]

Yes thats right the newest month goes to Column P. The earliest Month is in column E.

Column B, C and D can be discarded as it is aggregations, which can be achived from the rest of the columns.

There are no further columns right of P .

Thanks Mate 

[LRuCelver · Accepted solution]

So columns A to D have static headers, B to D can be discarded and E to P have the data with the dates as the header?

In that case you can use this:

Data:
NoConcatenate Load
    *
From [lib://AttachedFiles/Table.xlsx]
(ooxml, embedded labels, table is Sheet1);

Drop Fields
	discardMe1,
    discardMe2,
    discardMe3
From Data;



CreateCrosstable:
CrossTable(YearMonthText, Value, 1) Load
	*
Resident Data;

Drop Table Data;



FinalTable:
NoConcatenate Load
	Date(Date#(YearMonthText, 'YYYY-MMM'), 'YYYY-MMM') as YearMonth,
    metric as Metric,
    Value
Resident CreateCrosstable;

Drop Table CreateCrosstable;

[Pratik81]

Thank you 

[LRuCelver]

You can simplify it even further:

CrossTableData:
CrossTable(YearMonthText, Value, 1) Load
    *
From [lib://AttachedFiles/Table.xlsx]
(ooxml, embedded labels, table is Sheet1);


FinalTable:
NoConcatenate Load
	Date(Date#(YearMonthText, 'YYYY-MMM'), 'YYYY-MMM') as YearMonth,
    metric as Metric,
    Value
Resident CrossTableData
Where not Match(YearMonthText, 'discardMe1', 'discardMe2', 'discardMe3');

Drop Table CrossTableData;

[Pratik81]

Was able to make some adaptations. Thanks so much for the help.