How to convert a crosstab excel file that has dynamic header data

[vvira1316]

Hi,

A colleague is formatting a raw data file (converting two row header 'Actual' & '7/31/2015' into a single row header 'Actual-[7/31/2015]' and removing some extra columns & rows eg. Totals)  and providing me crosstab source file that I've to use in QV app. Column headers of formatted file are as follows.

First 6 columns are Qualifier Fields for crosstable conversion

Entity
CostCenter
Acct
AcctDescription
D/C
Type
Actual-[7/31/2015]
Actual-[8/31/2015]
Actual-[9/30/2015]
Actual-[10/31/2015]
Actual-[11/30/2015]
Actual-[12/31/2015]
Actual-[1/31/2016]
Actual-[2/29/2016]
Actual-[3/31/2016]
Actual-[4/30/2016]
Actual-[5/31/2016]
Actual-[6/30/2016]
Actual-[7/31/2014]
Actual-[8/31/2014]
Actual-[9/30/2014]
Actual-[10/31/2014]
Actual-[11/30/2014]
Actual-[12/31/2014]
Actual-[1/31/2015]
Actual-[2/29/2015]
Actual-[3/31/2015]
Actual-[4/30/2015]
Actual-[5/31/2015]
Actual-[6/30/2015]
Actual-[7/31/2013]
Actual-[8/31/2013]
Actual-[9/30/2013]
Actual-[10/31/2013]
Actual-[11/30/2013]
Actual-[12/31/2013]
Actual-[1/31/2014]
Actual-[2/29/2014]
Actual-[3/31/2014]
Actual-[4/30/2014]
Actual-[5/31/2014]
Actual-[6/30/2014]
Actual-[7/31/2012]
Actual-[8/31/2012]
Actual-[9/30/2012]
Actual-[10/31/2012]
Actual-[11/30/2012]
Actual-[12/31/2012]
Actual-[1/31/2013]
Actual-[2/29/2013]
Actual-[3/31/2013]
Actual-[4/30/2013]
Actual-[5/31/2013]
Actual-[6/30/2013]
Budget-[7/31/2015]
Budget-[8/31/2015]
Budget-[9/30/2015]
Budget-[10/31/2015]
Budget-[11/30/2015]
Budget-[12/31/2015]
Budget-[1/31/2016]
Budget-[2/29/2016]
Budget-[3/31/2016]
Budget-[4/30/2016]
Budget-[5/31/2016]
Budget-[6/30/2016]

How can I dynamically handle period changes in the read for different period file. (I mean load script reading) There is a possibility that number of columns in the source may change in the future period file.

CrossTable(FieldDesc, Value, 6)
LOAD Entity,
CostCenter,
Acct,
AcctDescription,
[D/C],
Type,
"Actual-[7/31/2015]",  // text from this line and below will change in future month's source file
"Actual-[8/31/2015]",
"Actual-[9/30/2015]",
"Actual-[10/31/2015]",
"Actual-[11/30/2015]",
"Actual-[12/31/2015]",
"Actual-[1/31/2016]",
"Actual-[2/29/2016]",
"Actual-[3/31/2016]",
"Actual-[4/30/2016]",
"Actual-[5/31/2016]",
"Actual-[6/30/2016]",
"Actual-[7/31/2014]",
"Actual-[8/31/2014]",
"Actual-[9/30/2014]",
"Actual-[10/31/2014]",
"Actual-[11/30/2014]",
"Actual-[12/31/2014]",
"Actual-[1/31/2015]",
"Actual-[2/29/2015]",
"Actual-[3/31/2015]",
"Actual-[4/30/2015]",
"Actual-[5/31/2015]",
"Actual-[6/30/2015]",
"Actual-[7/31/2013]",
"Actual-[8/31/2013]",
"Actual-[9/30/2013]",
"Actual-[10/31/2013]",
"Actual-[11/30/2013]",
"Actual-[12/31/2013]",
"Actual-[1/31/2014]",
"Actual-[2/29/2014]",
"Actual-[3/31/2014]",
"Actual-[4/30/2014]",
"Actual-[5/31/2014]",
"Actual-[6/30/2014]",
"Actual-[7/31/2012]",
"Actual-[8/31/2012]",
"Actual-[9/30/2012]",
"Actual-[10/31/2012]",
"Actual-[11/30/2012]",
"Actual-[12/31/2012]",
"Actual-[1/31/2013]",
"Actual-[2/29/2013]",
"Actual-[3/31/2013]",
"Actual-[4/30/2013]",
"Actual-[5/31/2013]",
"Actual-[6/30/2013]",
"Budget-[7/31/2015]",
"Budget-[8/31/2015]",
"Budget-[9/30/2015]",
"Budget-[10/31/2015]",
"Budget-[11/30/2015]",
"Budget-[12/31/2015]",
"Budget-[1/31/2016]",
"Budget-[2/29/2016]",
"Budget-[3/31/2016]",
"Budget-[4/30/2016]",
"Budget-[5/31/2016]",
"Budget-[6/30/2016]"

FROM
[..\FinData.xlsx]
(ooxml, embedded labels, table is FinData)

Any guidance will be helpful.

Thanking you in advance.

[Josh_Good · Accepted solution]

Hi Vijay,

Try this:

CrossTable(FieldDesc, Value, 6)
LOAD *

FROM
[..\FinData.xlsx]
(ooxml, embedded labels, table is FinData)

This way you will load all fields in the table.

-Josh

Qlik

[vvira1316]

Hi Josh,

Yes, it will work. Thanks for your response.

Vijay

[sunil_sawargave]

Hi,

I have one table having different columns , that table load in application using data load editor, then how to unpivot that table & where to write the script in existing script.